SQL Server Agent 作業排程實現資料庫定時自動備份（含動態檔名）

手動備份既繁瑣又易忘，善用 SQL Server Agent 作業 即可建立全自動、可監控、支援動態檔名的備份機制。

快速建立步驟（SSMS / 企業管理器通用）

1. 展開 管理 → SQL Server Agent → 作業，右鍵「新增作業」。
2. 一般：名稱「每日自動備份」，擁有者 sa。
3. 步驟 → 新增：
   • 類型：Transact-SQL 指令碼
   • 資料庫：master（或目標 DB）
   • 指令：

BACKUP DATABASE [YourDB] TO DISK = N'F:\Backup\YourDB.bak'
WITH NOINIT, NOUNLOAD, NAME = N'DailyBackup', NOSKIP, STATS = 10, NOFORMAT;

4. 排程 → 新增：名稱「每日凌晨」→ 週期性 → 每天 → 02:00 執行。
5. 確定建立後，右鍵作業 → 啟動作業 測試。
6. 確認 SQL Server Agent 服務 為「自動」且已啟動。

進階：每日產生唯一檔名（避免覆蓋）

修改步驟指令為動態 SQL：

DECLARE @FileName NVARCHAR(200);
SET @FileName = N'F:\Backup\YourDB_' + CONVERT(CHAR(8), GETDATE(), 112) + N'.bak';
PRINT @FileName;
BACKUP DATABASE [YourDB] TO DISK = @FileName
WITH NOINIT, NOUNLOAD, NAME = N'DailyBackup', NOSKIP, STATS = 10, NOFORMAT;

產生檔名如 YourDB_20240506.bak，便於保留多日歷史備份。

進階：保留 N 天自動清理

同一作業新增第二步驟（PowerShell / xp_cmdshell 需啟用）：

-- PowerShell 方式（需 SQL Server 2008+ / xp_cmdshell 啟用）
EXEC xp_cmdshell 'powershell -command "Get-ChildItem F:\Backup\YourDB_*.bak | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-7)} | Remove-Item -Force"';

或建立獨立維護計畫/作業執行清理，避免備份步驟失敗影響清理。

驗證與監控

• 作業歷程記錄：右鍵作業 → 「檢視歷程記錄」確認成功/失敗。
• 設定作業「通知」：失敗時傳送 Email 給 DBA（需設定 Database Mail / SQLMail）。
• 定期執行 RESTORE VERIFYONLY FROM DISK = '...' 驗證備份檔可用性。

常見問題排查

建立完成後，資料庫備份將全自動化，DBA 僅需定期抽查歷程與還原演練即可高枕無憂。