欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-18 14:29:27
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                       --删除子文件夹中的文件