SqlServer批量备份多个数据库且删除3天前的备份

时间:2021-10-13 03:40:00

废话不多说了,直接给大家贴代码了,具体代码如下所示:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
/*******************************************
 * 批量备份数据库且删除3天前的备份
 *******************************************/
DECLARE @backupfile VARCHAR(1024) 
DECLARE @backdesc VARCHAR(1024) 
DECLARE @filename VARCHAR(1024) 
DECLARE @path VARCHAR(1024) 
DECLARE @dbname VARCHAR(1024) 
DECLARE @extension_name VARCHAR(16) 
--备份参数 
DECLARE tmp_Cur CURSOR
FOR
  SELECT NAME
  FROM  [sys].[databases] 
  WHERE  NAME NOT IN ( 'master', 'model','msdb','tempdb'
SET @path = N'D:\Backup\Autoback\'; 
SET @extension_name = N'bak'; 
--生成文件名 
SET @filename = CONVERT(VARCHAR(1024), GETDATE(), 120) 
SET @filename = REPLACE(@filename, ':', '') 
SET @filename = REPLACE(@filename, '-', '') 
SET @filename = REPLACE(@filename, ' ', '') 
SET @filename = @filename + '_' + CONVERT (VARCHAR(3), DATEPART(ms, GETDATE())) 
  + N'.' + @extension_name 
OPEN tmp_Cur; 
FETCH NEXT FROM tmp_Cur INTO @dbname; 
WHILE @@FETCH_STATUS = 0  
  BEGIN 
    -- 得到完整目标文件,数据库将备份到这个文件中 
    SET @backupfile = @path + @dbname + @filename 
    --SELECT @backupfile 
    SET @backdesc =@dbname + N'-完整 数据库 备份
    -- 开始备份, COMPRESSION 参数表示压缩,可节省磁盘空间 
    BACKUP DATABASE @dbname TO DISK = @backupfile WITH NOFORMAT, NOINIT, NAME = @backdesc, SKIP, NOREWIND, NOUNLOAD, STATS = 10, COMPRESSION 
    FETCH NEXT FROM tmp_Cur INTO @dbname 
  END 
CLOSE tmp_Cur; 
DEALLOCATE tmp_Cur; 
-- 删除3天前的备份文件 
DECLARE @olddate DATETIME 
SELECT @olddate = DATEADD(d, -3, GETDATE()) 
-- 执行删除 (SQL 2008 具备) 
EXECUTE master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1
[sql] view plain copy print?
--作业定时压缩脚本支持多库
DECLARE @DatabaseName NVARCHAR(50)
DECLARE @ExecuteSql NVARCHAR(MAX)
SET @ExecuteSql=''
DECLARE name_cursor CURSOR
FOR
  SELECT name FROM master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 
  'northwind','pubs','AgentSys','ydttimedtask','YiDianTongV2' ) 
OPEN name_cursor; 
FETCH NEXT FROM name_cursor INTO @DatabaseName; 
WHILE @@FETCH_STATUS = 0
  BEGIN  
    SET @ExecuteSql =''
    SET @ExecuteSql +='
      USE ['+@DatabaseName+'];
      DECLARE @Error INT
      SET @Error=(SELECT TOP 1 size/128.0 - CAST(FILEPROPERTY([NAME], ''SpaceUsed'') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files ORDER BY [NAME] DESC)
      --PRINT @Error
      IF(@Error>1)
        BEGIN
          ALTER DATABASE ['+@DatabaseName+']  --数据库名字
          SET RECOVERY SIMPLE;  --设置简单恢复模式
          DBCC SHRINKFILE ([YiDianTongV2], 1);  --(M)不能小于1M,
          DBCC SHRINKFILE ([YiDianTongV2_log], 1);  --(M)不能小于1M
          ALTER DATABASE ['+@DatabaseName+']
          SET RECOVERY FULL;  --恢复为原来完整模式
        END
    '
    PRINT @ExecuteSql; --打印
    EXEC(@ExecuteSql) --执行
    FETCH NEXT FROM name_cursor INTO @DatabaseName; 
  END
CLOSE name_cursor; 
DEALLOCATE name_cursor;

总结

以上所述是小编给大家介绍的SqlServer批量备份多个数据库且删除3天前的备份,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!

原文链接:http://blog.csdn.net/smartsmile2012/article/details/78040900