windows任务计划运行sql脚本备份数据库

一、编写sql脚本

DECLARE @dbs TABLE(
    rowNum INT IDENTITY(1, 1) PRIMARY KEY,
    dbName NVARCHAR(200),
    bakPath AS N'E:\DbBackup\' + dbName + '\' + dbName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), GETDATE(), 120), '-', ''),':',''),' ','_') + '.bak'
)
SET NOCOUNT ON
--将所有符合条件的DB插入到表变量
INSERT INTO @dbs(dbName)
SELECT NAME
FROM   sys.databases d
WHERE  NAME NOT IN ('master', 'tempdb', 'model', 'msdb', 'ag_test')
       AND NAME NOT LIKE '%monitor%'
ORDER BY
       NAME
--遍历表变量中的所有数据库
DECLARE @i INT,@iMax INT,@sql NVARCHAR(MAX)
SELECT @i = 1,@iMax = MAX(rowNum) FROM @dbs
WHILE @i <= ISNULL(@iMax, 0)
BEGIN
    --创建 dbName 开头的子文件夹
    SELECT @sql = 'EXECUTE master.dbo.xp_create_subdir N''E:\DbBackup\' + 
           dbName + ''''
    FROM   @dbs
    WHERE  rowNum = @i
    
    EXEC (@sql)
    --备份
    --2005 要去掉 COMPRESSION
    SELECT @sql = 'BACKUP DATABASE [' + dbName + '] TO  DISK = N''' + bakPath + 
           ''' WITH NOFORMAT, NOINIT,NAME = N''wxh-Full Database Backup'', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 20'        
    FROM   @dbs
    WHERE  rowNum = @i
    
    EXEC (@sql)
    SET @i = @i + 1
END
--作业步骤二. 删除过期文件
DECLARE @oldDate DATETIME
--保留 2 天文件（昨天0点之前文件会被删除）
SET @oldDate = CAST(CONVERT(CHAR(10),GETDATE(),120) AS DATETIME)-120
EXECUTE MASTER.dbo.xp_delete_file 
0,                        --0: 备份文件，1: 维护计划文本报告
N'E:\DbBackup\',    --文件路径
N'bak',                 --文件扩展名
@oldDate,               --在此时间之前的文件一律删除
1                       --删除子文件夹中的文件

 

二、编写.bat文件

SET SQLCMD="D:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SET PATH="C:\Users\Administrator\Desktop\backupdata\"
SET SERVER="192.168.10.243,5781"
SET DB="master"
SET LOGIN="sa"
SET PASSWORD="Aa123456.."
SET OUTPUT="C:\Users\Administrator\Desktop\backupdata\OutputLog.txt"

ECHO %date% %time% > %OUTPUT%

for /r %PATH% %%i in (*.sql) do (
%SQLCMD% -S %SERVER% -d %DB% -U %LOGIN% -P %PASSWORD% -i %%i >> %OUTPUT%
)

三、windows任务计划

创建基本任务，设置触发器。