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

  /*******************************************    * 批量备份数据库且删除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:BackupAutoback';    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())    -- 执行删除 ( 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天前的备份,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对沃谷博客网站的支持!

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注