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

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

2020-05-22 15:41smartsmile2012 Sql Server

这篇文章主要介绍了SqlServer批量备份多个数据库且删除3天前的备份,需要的朋友可以参考下

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

				?

									/******************************************* 

									 * 批量备份数据库且删除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;