MSSQL 資料庫定時自動備份完整教學：腳本排程與維護計劃兩大方案

前言

資料庫備份是資料安全的最後一道防線。對於 SQL Server (MSSQL) 而言，建立可靠的定時自動備份機制至關重要。本文將詳細介紹兩種主流方案：使用 T-SQL 腳本搭配 SQL Server Agent 作業排程（適用於高度客製化需求），以及透過企業管理器的資料庫維護計劃（適用於圖形化快速設定）。

方案一：T-SQL 腳本 + SQL Server Agent 作業（進階彈性方案）

此方案利用 xp_cmdshell 建立網路磁碟機映射，將備份檔寫入遠端共享資料夾，並能自動清理舊備份，適合跨伺服器備份或需精細控制備份邏輯的場景。

核心邏輯與腳本解析

以下範例展示將伺服器 192.168.1.8 上的 TEST 資料庫備份至 192.168.1.145 的 C$ 共享目錄：

-- 1. 建立網路映射 (映射為 Z:)
EXEC master..xp_cmdshell 'net use z: \\192.168.1.145\c$ "Password" /user:192.168.1.145\administrator';

-- 2. 執行備份
BACKUP DATABASE TEST TO DISK = 'z:\Test.bak' WITH INIT;

-- 3. 移除映射
EXEC master..xp_cmdshell 'net use z: /delete';

進階：自動備份所有使用者資料庫並刪除 4 天前舊檔

將以下腳本放入 SQL Server Agent 作業步驟中，可實現全自動化：

-- 建立映射 (假設目標共享為 DatabaseBackup$，帳號 Roy)
EXEC master..xp_cmdshell 'net use w: \\BackupServer\DatabaseBackup$ "passWord" /user:Roy', NO_OUTPUT;
GO

DECLARE @BackupCmd NVARCHAR(200), @DelCmd NVARCHAR(200);
DECLARE @DBName NVARCHAR(128);

-- 遊標遍歷所有非系統資料庫 (dbid > 4)
DECLARE db_cursor CURSOR FOR
SELECT name FROM master..sysdatabases WHERE dbid > 4;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 動態產生備份指令：資料庫名_YYYYMMDD.bak
    SET @BackupCmd = 'BACKUP DATABASE ' + QUOTENAME(@DBName) + 
                     ' TO DISK = ''W:\' + @DBName + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'' WITH INIT';
    
    -- 動態產生刪除 4 天前備份指令
    SET @DelCmd = 'EXEC master..xp_cmdshell ''DEL W:\' + @DBName + '_' + 
                  CONVERT(VARCHAR(8), DATEADD(DAY, -4, GETDATE()), 112) + '.bak'', NO_OUTPUT';

    -- 先刪舊再備新
    EXEC (@DelCmd);
    EXEC (@BackupCmd);

    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- 移除映射
EXEC master..xp_cmdshell 'net use w: /delete';
GO

建立排程作業步驟 (SQL Server 2000/2005/2008 通用)

1. 開啟 企業管理器 (或 SSMS) → 管理 → SQL Server Agent → 作業 → 右鍵 新增作業。
2. 一般 頁面：輸入作業名稱 (如「每日全庫遠端備份」)，擁有者選 sa。
3. 步驟 頁面：新增步驟 → 類型選 Transact-SQL 指令碼 (TSQL) → 資料庫選 master → 命令框貼上上述完整腳本 → 點選 剖析 驗證語法。
4. 排程 頁面：新增排程 → 設定頻率 (如每日零晨 02:00 執行)。
5. 確保 SQL Server Agent 服務 為「啟動」且設為「自動」。

方案二：資料庫維護計劃 (圖形化精靈，快速上手)

適合不想寫代碼、需求標準的 DBA。

1. 企業管理器 → 管理 → 資料庫維護計劃。
2. 右鍵新增計劃，按精靈逐步勾選：備份資料庫 (完整/差異/記錄)、檢查資料庫完整性、清除舊備份檔案 等任務。
3. 指定備份目標資料夾 (支援本地或 UNC 網路路徑)、副本保留天數、排程時間。
4. 完成後系統自動建立對應的 Agent 作業，可在作業監視器中查看執行紀錄。

關鍵注意事項與最佳實踐

• 權限問題：執行備份的 SQL Server 服務帳號 (或 Agent 代理帳號) 必須對目標備份資料夾擁有 讀寫/修改 權限。跨網域/工作組環境建議建立同名同密碼的本地帳號雙向信任。
• xp_cmdshell 風險：此擴展預設關閉。啟用時請評估安全性，生產環境建議改用 Proxy 帳號 或 CLR 整合 替代。
• 備份驗證：定期執行 RESTORE VERIFYONLY 或實際還原演練，確保備份檔可用性。
• 磁碟空間：監控備份目標磁碟剩餘空間，避免寫入失敗。

總結

方案一 靈活度最高，支援複雜邏輯 (如差異備份鏈、加密、壓縮、多目標複製)，適合有開發能力的團隊；方案二 部署快、維護易，適合中小型環境或標準化備份策略。無論選擇哪種，定期演練還原 才是備份策略成功的唯一標準。