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:BackupAutoback';  
SET @extension_name = N'bak';  
  
--生成文件名  
SET @filename = CONVERT(VARCHAR(1024),GETDATE(),120)  
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

--作业定时压缩脚本支持多库
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','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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!