SQLServer维护计划：用语句来创建多个数据库的备份、删除过期文件作业

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                       --删除子文件夹中的文件