冠霖的部落格

-- 取得資料庫本機的 Backup 資料目錄
DECLARE @backup_path nvarchar(256);
DECLARE @backup_file nvarchar(256);
SET @backup_path = (SELECT SUBSTRING(SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1), 1, CHARINDEX(N'\DATA\', LOWER(physical_name)) - 1) + '\Backup\'
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);


-- 用來暫存備份指令的變數
declare @backup_sql nvarchar(max)
set @backup_sql = '';

-- 用來暫存資料庫名稱的變數
declare @dbname nvarchar(256)

-- 宣告 cursor 以取得資料庫名稱
declare icur cursor static for select name from sys.databases where name != 'tempdb'

OPEN icur

fetch next from icur into @dbname
while(@@FETCH_STATUS=0)
BEGIN
    set @backup_file = @backup_path + @dbname + '.bak'
    set @backup_sql  = 'BACKUP DATABASE ['+@dbname+']  TO DISK='''+@backup_file+''''

    exec (@backup_sql)

    FETCH NEXT FROM icur INTO @dbname
END

close icur
deallocate icur

備份完成的資料庫預設會擺在 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup 目錄下。