廢話不多說了，直接給大家貼代碼了，具體代碼如下所示：

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