如何一次備份 SQL Server 中所有資料庫

-- 取得資料庫本機的 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 目錄下。