SQL Server備份及壓縮所有資料庫指令碼
這段指令可以將SQL Server非系統資料庫壓縮、備份至硬碟上，並刪除7天以前的檔案。
1: CREATE procedure [dbo].[proc_Backup_All_DataBase]
2: AS
3: SET NOCOUNT ON;
4:
5: --儲存路徑
6: DECLARE @path varchar(100)='C:\SQL_DATA\Backup\'
7:
8: --取得現在時間
9: DECLARE @nowdate varchar(10)=rtrim(CONVERT(char, getdate(), 112))
10:
11: -- 用來暫存資料庫名稱的變數
12: DECLARE @dbname nvarchar(256)
13:
14: DECLARE icur cursor static for select name from sys.databases where name not in ( 'master', 'model', 'msdb', 'tempdb' )
15: OPEN icur
16:
17: FETCH NEXT FROM icur INTO @dbname
18: WHILE(@@FETCH_STATUS=0)
19: BEGIN
20: DECLARE @SQLString nvarchar(3000);
21: -- 壓縮資料庫
22: SET @SQLString='DBCC SHRINKDATABASE( ['+@dbname+'] , 10);';
23: EXEC sp_executesql @SQLString
24:
25:
26: -- 備份資料庫
27: DECLARE @file_name varchar(100)=@path+@dbname+@nowdate+'.bak';
28:
29: SET @SQLString='BACKUP DATABASE ['+@dbname+']
30: TO DISK = '''+@file_name+'''
31: WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10;';
32:
33: EXEC sp_executesql @SQLString
34:
35: FETCH NEXT FROM icur INTO @dbname
36:
37: END
38:
39: CLOSE icur
40: DEALLOCATE icur
41:
42:
43: --刪除7天前的備份
44: DECLARE @deldate varchar(10)= CONVERT(char, DATEADD(day,-6,GETDATE()),111)
45: DECLARE @d varchar(20)=CONVERT(char, getdate(), 111)
46: EXECUTE master.dbo.xp_delete_file 0,@path,N'bak',@deldate