USE master
go
DECLARE @bak_path NVARCHAR(200)
DECLARE @bak_file_name NVARCHAR(200)
--设置文件备份路径
SET @bak_path = 'D:\tools\DataBase\'
--利用游标遍历,逐个备份数据库
DECLARE @db_name SYSNAME
DECLARE cur_database CURSOR FOR
SELECT [name]-- 查询所有数据库
FROM sys.databases
WHERE [state] = 0 -- 0 = ONLINE 在线状态
AND [name] NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'ReportServer','ReportServerTempDB')
--系统数据库、演示数据库除外
OPEN cur_database
FETCH NEXT FROM cur_database INTO @db_name
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
--设置备份文件名称,形如:dbname_2011-10-09.bak
SET @bak_file_name = @bak_path + '' + @db_name + '_'
+ CONVERT(VARCHAR(10), GETDATE(), 120) + '.bak'
--开始完整备份
BACKUP DATABASE @db_name TO DISK = @bak_file_name
FETCH NEXT FROM cur_database INTO @db_name
END
CLOSE cur_database
DEALLOCATE cur_database