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

erina.pan

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

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

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

那用语句如何实现呢？

1. --作业步骤1. 备份所有指定数据库
   
2. DECLARE @dbs TABLE(
   
3.     rowNum INT IDENTITY(1, 1) PRIMARY KEY,
   
4.     dbName NVARCHAR(200),
   
5.     bakPath AS N'D:\database_bak\' + dbName + '\' + dbName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), GETDATE(), 120), '-', ''),':',''),' ','_') + '.bak'
   
6. )
   
7. SET NOCOUNT ON
   
8. --将所有符合条件的DB插入到表变量
   
9. INSERT INTO @dbs(dbName)
   
10. SELECT NAME
    
11. FROM   sys.databases d
    
12. WHERE  NAME NOT IN ('master', 'tempdb', 'model', 'msdb', 'ag_test')
    
13.        AND NAME NOT LIKE '%monitor%'
    
14. ORDER BY
    
15.        NAME
    
16. 
    
17. --遍历表变量中的所有数据库
    
18. DECLARE @i INT,@iMax INT,@sql NVARCHAR(MAX)
    
19. SELECT @i = 1,@iMax = MAX(rowNum) FROM @dbs
    
20. 
    
21. WHILE @i <= ISNULL(@iMax, 0)
    
22. BEGIN
    
23.     --创建 dbName 开头的子文件夹
    
24.     SELECT @sql = 'EXECUTE master.dbo.xp_create_subdir N''d:\database_bak\' +
    
25.            dbName + ''''
    
26.     FROM   @dbs
    
27.     WHERE  rowNum = @i
    
28.      
    
29.     EXEC (@sql)
    
30.     --备份
    
31.     --2005 要去掉 COMPRESSION
    
32.     SELECT @sql = 'BACKUP DATABASE [' + dbName + '] TO  DISK = N''' + bakPath +
    
33.            ''' WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 20'     
    
34.     FROM   @dbs
    
35.     WHERE  rowNum = @i
    
36.      
    
37.     EXEC (@sql)
    
38.     SET @i = @i + 1
    
39. END
    
40. 
    
41. --作业步骤二. 删除过期文件
    
42. DECLARE @oldDate DATETIME
    
43. --保留 2 天文件（昨天0点之前文件会被删除）
    
44. SET @oldDate = CAST(CONVERT(CHAR(10),GETDATE(),120) AS DATETIME)-1
    
45. EXECUTE MASTER.dbo.xp_delete_file
    
46. 0,                      --0: 备份文件，1: 维护计划文本报告
    
47. N'D:\database_bak\',    --文件路径
    
48. N'bak',                 --文件扩展名
    
49. @oldDate,               --在此时间之前的文件一律删除
    
50. 1                       --删除子文件夹中的文件

复制代码