SQL Server Agent 作業排程實現資料庫自動備份

透過 SQL Server Agent 作業 搭配 存儲過程，可建立靈活、免維運的資料庫自動備份機制，支援依日期動態產生檔名、條件式備份（如特定日期區間）等進階需求。

方案架構

1. 存儲過程：封裝備份邏輯、動態檔名、條件判斷。
2. Agent 作業：定義排程、呼叫存儲過程、錯誤處理與通知。
3. Agent 服務：必須啟動（自動啟動模式）。

步驟 1：建立動態備份存儲過程

CREATE PROCEDURE dbo.usp_AutoBackup
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @FileName NVARCHAR(200),
            @DayOfMonth INT = DAY(GETDATE());

    -- 動態檔名：F:\Backup\DB_YYYYMMDD.bak
    SET @FileName = N'F:\Backup\DB_' + CONVERT(CHAR(8), GETDATE(), 112) + N'.bak';

    -- 條件：每月 20 號至次月 10 號才備份（示例業務規則）
    IF @DayOfMonth >= 20 OR @DayOfMonth <= 10
    BEGIN
        PRINT '執行備份：' + @FileName;
        BACKUP DATABASE [YourDB] TO DISK = @FileName
            WITH INIT, NOUNLOAD, NAME = N'AutoBackup', NOSKIP, STATS = 10, NOFORMAT;
    END
    ELSE
        PRINT '非備份時段，略過。';
END

步驟 2：建立 Agent 作業

1. 企業管理器 / SSMS → 管理 → SQL Server Agent → 作業 → 新增作業。
2. 一般：名稱「每日自動備份」，擁有者 sa（或具備 sysadmin 的帳號）。
3. 步驟：新增步驟 → 類型 Transact-SQL → 資料庫選目標 DB → 指令輸入 EXEC usp_AutoBackup。
4. 排程：新增排程 → 類型「週期性」→ 頻率「每天」→ 時間設定（如凌晨 02:00）。
5. 通知（選填）：失敗時寄送郵件給 DBA。

步驟 3：啟用 Agent 服務

• 服務管理器（SQL Server 2000）或組態管理器（2005+）將 SQL Server Agent 設為自動並啟動。
• 作業建立完成後，右鍵作業 → 啟動作業 測試，查看記錄確認成功。

進階技巧

• 多資料庫備份：存儲過程加入游標迴圈遍歷 sys.databases。
• 保留天數清理：同一作業加入第二步驟呼叫 xp_delete_file 或 PowerShell 清理超過 N 天的 .bak。
• 差異/記錄備份：建立獨立作業，排程間隔較短（如每小時），WITH DIFFERENTIAL 或 BACKUP LOG。
• 備份驗證：RESTORE VERIFYONLY FROM DISK = @FileName 寫入步驟末端。

常見坑點

1. Agent 服務未啟動 → 作業永不執行。
2. 備份路徑權限不足 → SQL Server 服務帳號需對資料夾有寫入權限。
3. 磁碟空間耗盡 → 監控磁碟剩餘空間或設定作業步驟失敗通知。
4. 排程衝突 → 避免完整、差異、記錄備份同一時間重疊。

建立完成後，資料庫備份將全自動化，DBA 僅需定期抽查備份檔案完整性與還原演練即可。