/*******************************************  * 批量備份數據庫且刪除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;