?

USE [master]




------删除旧数据-------------------------------------------------------------
DECLARE @delTime VARCHAR(100)
SET @delTime = CONVERT(VARCHAR(100), GETDATE(), 126) -----当前时间
EXECUTE MASTER.dbo.xp_delete_file 0,'D:\DataBak','bak',@delTime,1






DECLARE @FILENAME VARCHAR(500)


DECLARE @DATABakPath VARCHAR(500)


DECLARE @DATABaseName VARCHAR(500)


DECLARE @DATAFullName VARCHAR(500)


SET @DATABakPath = 'D:\DataBak\'


DECLARE @temp TABLE(a INT, b INT, c INT)


DECLARE @exeText VARCHAR(100)


SET @FILENAME = REPLACE(
REPLACE(
REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''),
' ',
''
),
':',
''
) + '.bak'









------------遍历所有用户数据库 进行备份-------------------------------------------------------------------


DECLARE My_Cursor CURSOR --定义游标
FOR
(
SELECT NAME
FROM sys.databases
WHERE NAME NOT IN ('master', 'msdb', 'tempdb', 'model',
'ReportServerTempDB', 'ReportServer')
) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @DATABaseName; --读取第一行数据
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DATAFullName = @DATABakPath + @DATABaseName

SET @exeText = 'mkdir ' + @DATAFullName --创建备份目录
EXEC xp_cmdshell @exeText,
no_output

SET @DATAFullName = @DATAFullName + '\' + @FILENAME + ''

BACKUP DATABASE @DATABaseName TO DISK = @DATAFullName

FETCH NEXT FROM My_Cursor INTO @DATABaseName; --读取下一行数据
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标


PRINT '-----------备份完成---------------------' + CONVERT(VARCHAR(100), GETDATE(), 126)+ '---------------'

--------------------代码结束-------------

SQL一键备份用户数据库

?