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

数据库  /  管理员 发布于 6年前   151

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

/*******************************************  * 批量备份数据库且删除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天前的备份，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！