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

数据运维 2023-04-21 Escape 手机阅读

废话不多说了，直接给大家贴代码了，具体代码如下所示： /*** * 批量备份数据库且删除3天前的备份 ***/ DECLARE @backupfile VA

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

/***

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