用语句来创建多个数据库的备份、删除过期文件作业

SQLServer的维护计划是强大的，特别是体现在备份、删除过期文件这个方面。

可以创建文件夹、备份多个库、删除过期文件……

不过配置时还是有点烦人，要选择很多东西。

那用语句如何实现呢？

--作业步骤1. 备份所有指定数据库
DECLARE @dbs TABLE(
    rowNum INT IDENTITY(1, 1) PRIMARY KEY,
    dbName NVARCHAR(200),
    bakPath AS N'D:\database_bak\' + 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''d:\database_bak\' + 
           dbName + ''''
    FROM   @dbs
    WHERE  rowNum = @i
    
    EXEC (@sql)
    --备份
    --2005 要去掉 COMPRESSION
    SELECT @sql = 'BACKUP DATABASE [' + dbName + '] TO  DISK = N''' + bakPath + 
           ''' WITH NOFORMAT, NOINIT, 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)-1
EXECUTE MASTER.dbo.xp_delete_file 
0,						--0: 备份文件，1: 维护计划文本报告
N'D:\database_bak\',	--文件路径
N'bak',                 --文件扩展名
@oldDate,               --在此时间之前的文件一律删除
1                       --删除子文件夹中的文件