SQL Server Agent 作業排程實現自動備份：含日期動態檔名、壓縮與驗證完整腳本

透過 SQL Server Agent 作業排程可實現無人值守的資料庫自動備份。本文提供完整建立步驟、動態日期檔名腳本、壓縮/驗證選項與啟用服務檢查，確保備份可靠可追蹤。

建立作業步驟

1. SSMS 連線 → 展開「SQL Server Agent」→ 右鍵「作業」→ 「新增作業」。
2. 「一般」頁：名稱「每日完整備份」，擁有者 sa（或具備 sysadmin 的帳號）。
3. 「步驟」頁 → 「新增」：步驟名稱「執行備份」，類型 Transact-SQL 指令碼，資料庫 master，命令貼上下方動態腳本。
4. 「排程」頁 → 「新增」：名稱「每日凌晨 02:00」，頻率「每日」，每日頻率「發生一次 02:00:00」。
5. 確定後，右鍵作業「啟用作業」。
6. 確認 SQL Server Agent 服務為「執行中」且啟動類型為「自動」。

動態日期檔名備份腳本（推薦）

DECLARE @FileName NVARCHAR(260);
SET @FileName = N'E:\Backup\DB_Backup_' 
    + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ' ', ''), ':', '')
    + N'.bak';

PRINT @FileName;  -- 除錯用，可註解

BACKUP DATABASE [YourDB]
TO DISK = @FileName
WITH
    INIT,                   -- 覆寫同名檔案（配合唯一檔名實際不會重複）
    NOFORMAT,
    NOINIT,
    NAME = N'YourDB_Full_Backup',
    SKIP,
    NOREWIND,
    NOUNLOAD,
    COMPRESSION,            -- SQL 2008+ 支援備份壓縮，節省 60-80% 空間
    CHECKSUM,               -- 啟用頁面檢查碼驗證
    STATS = 10;             -- 每 10% 顯示進度

腳本關鍵選項說明

替代方案：維護計畫

SSMS → 「管理」→ 「維護計畫」→ 「維護計畫精靈」，視覺化設定備份、清除舊檔、完整性檢查、索引重組，自動產生 SSIS 封裝並部署至 Agent 作業，適合偏好 GUI 的 DBA。

備份後驗證腳本（可加入第二步驟）

RESTORE VERIFYONLY 
FROM DISK = @FileName
WITH CHECKSUM, STATS = 10;

若驗證失敗，作業會標示失敗並發送警示（需設定作業通知）。

清除舊備份檔案（PowerShell/xp_cmdshell）

-- 保留最近 7 天
EXEC xp_cmdshell 'forfiles /p "E:\Backup" /m "DB_Backup_*.bak" /d -7 /c "cmd /c del @path"';

⚠️ 需啟用 xp_cmdshell 且服務帳號具備資料夾刪除權限。生產環境建議改用 Agent 代理商 Proxy 執行 PowerShell 腳本更安全。

常見坑點提醒

• ❌ 忘記啟用 SQL Server Agent 服務 → 作業永不執行。
• ❌ 備份路徑不存在或服務帳號無寫入權限 → 作業失敗。
• ❌ 磁碟空間不足 → 監控磁碟剩餘空間，設定警示。
• ❌ 僅做完整備份，無差異/記錄備份 → RPO 風險大，建議結合差異備份（每 4-6 小時）+ 記錄備份（每 15-60 分鐘）。