如何定期對SQL Server中所有資料庫進行完整備份並發送EMAIL給特定人員
資料庫備份作業除了可以使用維護計畫進行外，可以透過sys.databasest查詢系統中有那些資料庫，搭配cursor逐個資料庫進行備份，也可彈性的設定資料庫備份檔的名稱，並在將備份結果以EMAIL通知DBA，下列程式碼即可達到上述的需求：
1: BEGIN TRY
2: DECLARE @DB sysname
3: DECLARE @DBPath nvarchar(120)
4: DECLARE db_cur CURSOR FOR
5: SELECT name
6: FROM sys.databases
7: WHERE name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks')
8: AND state = 0
9:
10: OPEN db_cur
11: FETCH NEXT FROM db_cur INTO @DB
12: WHILE (@@FETCH_STATUS = 0)
13: BEGIN
14: SET @DBPath = '\\FS01\DBBak\' + @DB
15: + '_' + DATENAME(WEEK,GETDATE())
16: + '_Full.BAK'
17: BACKUP DATABASE @DB TO DISK = @DBPath WITH INIT
18: FETCH NEXT FROM db_cur INTO @DB
19: END
20:
21: CLOSE db_cur
22: DEALLOCATE db_cur
23:
24: EXEC msdb.dbo.sp_notify_operator
25: @name = N'DBA',
26: @subject = N'SQL Server message on DB1',
27: @body = N'資料庫完整備份成功'
28: END TRY
29: BEGIN CATCH
30: EXEC msdb.dbo.sp_notify_operator
31: @name = N'DBA',
32: @subject = N'SQL Server message on DB1',
33: @body = N'資料庫完整備份失敗'
34: END CATCH
上述程式碼第5~8列用來從sys.databases系統檢視中查出SQL Server中所有系統及使用者資料庫，其中過濾掉一些不需備份的暫存資料庫及範例資料庫，另外state = 0用來過濾目前online的資料庫（詳見參考資料1）。接著第12~19列以迴圈方式逐個資料庫進行備份，備份檔檔名以資料庫名稱加上備份當時的系統日期在當年度的第幾週（例如：master_20_Full.bak）來命名。第24~27用來在備份成功時發EMAIL通知DBA，萬一備份失敗，則由第31至33列來發EMAIL通知DBA備份失敗。
只要將上述程式建立一個SQL Agaent Job，排定在特定時間進行資料庫完整備份，在此是以每週做一次完整備份為例，將來萬一新增任何使用者資料庫，也會自動納入備份機制，不須手動進行調整，若有資料庫不在需要定期備份，也只要將其狀態設為非Online即可自動在下次備份排程啟動時被略過。
參考資料：