備份計劃-備份資料庫工作

維護計畫的策略性價值

本文件的目的，在於為 IT 團隊建立一套標準化、可依循的 SQL Server 維護計畫設定與管理流程。 SQL Server 維護計畫是一項強大的自動化工具，對於確保資料庫的長期效能穩定、保障資料安全，以及主動預防潛在的系統風險，扮演著至關重要的角色。本 SOP 將引導團隊從被動、手動的維護模式，轉向為主動、自動化的管理典範，從而釋放寶貴的人力資源，專注於更高階的效能調校與系統架構優化。

在技術層面上，維護計畫（Maintenance Plan）是 SQL Server Management Studio (SSMS) 中內建的一套圖形化工作流程設計工具。它允許我們透過直觀的介面，將一系列複雜的資料庫維護任務（如備份、索引重組等）組合並排程執行。其運作的底層核心，是完全依賴 SQL Server Agent Job 來實現自動化的定時觸發與執行。

在我們深入探討各項維護任務之前，首要之務是確保執行環境已準備就緒。接下來的章節將詳細說明執行維護計畫前的必要準備工作。

前置準備作業：確保環境整備

在建立任何維護計畫之前，確保伺服器環境配置正確是至關重要的第一步。一個準備妥當的環境，是後續所有自動化計畫能夠穩定、可靠執行的基石。

請務必完成以下前置檢查項目：

啟動 SQL Server Agent 服務
維護計畫的所有排程與自動執行，完全依賴 SQL Server Agent 服務。請確保此服務正在執行。您可以透過執行 services.msc 開啟 Windows 服務應用程式，或經由「控制台 > 系統管理工具 > 服務」路徑找到它。對於任何生產環境的伺服器，其「啟動類型」必須設定為「自動」，以確保伺服器重啟後服務能無縫接續運作。

新版 SSMS 安裝注意事項
對於使用新版 SQL Server Management Studio（例如 21.x 或以上版本）的團隊成員，需要特別注意：若要使用維護計畫的圖形化介面功能，必須在安裝 SSMS 的過程中，選擇安裝所有元件，特別是 BI 相關元件。若未完整安裝，可能會導致「維護計畫」功能節點無法正常顯示或使用。

新的 SSMS 版本如 21 版，維護計劃的功能需要在安裝時選上，否則可戔會出現無法使用的問題。

若是想要用新版的 SSMS 21.x 或以上功能，又想要啟用維護計劃，則在安裝 SSMS 時需要把所有元件全部安裝，包含 BI 的元件。

在完成上述環境準備後，我們便可以開始深入了解計畫中可以包含的各項核心維護任務。

核心維護任務詳解

本章節將分類解析維護計畫中提供的各項核心任務。透徹理解每一項任務的具體功能與目的，是設計出一套全面且高效維護策略的基礎。

備份任務
備份是資料庫災難復原的基石，維護計畫提供了三種關鍵的備份類型：
- 資料庫完整備份 (Full Backup): 建立資料庫的完整複本，包含所有物件與資料。這是災難復原策略的起點。
- 差異備份 (Differential Backup): 僅備份自上一次「完整備份」以來發生變更的資料區塊。相較於完整備份，其檔案較小、執行速度較快。
- 交易紀錄備份 (Transaction Log Backup): 備份資料庫的交易日誌。此任務僅適用於「完整」或「大量記錄」復原模式的資料庫，它能提供將資料庫還原至特定時間點（Point-in-Time Recovery）的關鍵能力。
索引與統計維護
索引與統計資料的健康狀態，直接影響資料庫的查詢效能：
- 重建索引 (Rebuild Indexes): 透過刪除並重新建立索引的方式，徹底消除索引碎片。請注意：在 SQL Server Standard Edition 中，這通常是一項離線操作，會鎖定資料表並阻擋使用者存取。 Enterprise Edition 則提供線上重建的功能。
- 重組索引 (Reorganize Indexes): 以較輕量的方式重新組織索引分葉層級的頁面，使其符合邏輯順序。這是一項線上操作，對系統資源的耗用較低，適用於處理輕度碎片或無法接受服務中斷的時段。
- 更新統計資料 (Update Statistics): 統計資料是查詢最佳化器用來判斷最佳資料存取路徑（執行計畫）的依據。此任務會更新資料表與索引的資料分佈統計資訊，確保查詢最佳化器能做出最明智的決策。
一個常見的進階策略是使用腳本，先檢查索引的碎片程度：若碎片率中等（例如 10-30%）則執行重組，若碎片率高（>30%）則執行重建，並在任何索引維護任務完成後，務必更新統計資料，以確保查詢最佳化器能利用最新的索引結構資訊。
資料庫完整性與清理
確保資料庫本身的健康，並管理維護過程所產生的檔案與紀錄：
- Check Database Integrity: 此任務會執行 DBCC CHECKDB 命令，檢查資料庫中是否存在任何邏輯或實體的配置與結構損壞，是預防資料毀損的關鍵健康檢查。
- 清理任務: 維護計畫提供自動化的清理機制，可依據設定的保留期限，自動刪除過期的備份檔案、SQL Server Agent 的作業歷史記錄，以及維護計畫本身的執行記錄，避免磁碟空間被無限制地佔用。
在了解了這些可用的工具後，下一步的重點是如何將它們智慧地組合並安排成一套合理的執行排程。

最佳實踐：排程建議與範例

制定一個合理的執行排程至關重要。一個理想的排程，應在確保維護效果的同時，最大限度地減少對系統正常運作時段的效能衝擊。以下提供一個常見的排程範例，可作為設計維護計畫的基礎藍圖：

任務項目	建議頻率	建議執行時間與考量
資料庫完整備份	每天 `1` 次	深夜時段（系統負載最低時）。這是資源密集型操作，應避開業務高峰期。
交易紀錄備份	每 `15` 分鐘至 `1` 小時	全日執行。頻率取決於資料異動量與可接受的資料遺失風險 (`RPO`)。
重建或重組索引	每週 `1–2` 次	深夜時段或指定的維護窗口。此為高 `I/O` 操作，應在離峰時間執行。
更新統計資料	每週 `1` 次	建議於重建索引後執行。確保統計資料能反映索引整理後的最新狀態。
檢查資料庫完整性	每週或每月一次	深夜時段或指定的維護窗口。此操作會大量消耗 `I/O` 資源。
清除過期備份與記錄	每日或每週一次	任意時間。此操作對系統效能影響較小。

僅僅設定和執行計畫是不夠的。為確保這些自動化任務確實如預期般運作，後續的監控與驗證同等重要。

執行結果的監控與驗證

「未經監控的自動化是不可靠的」。定期檢查維護計畫的執行結果，是確保其有效性的必要環節，也是及早發現潛在問題（如備份失敗、磁碟空間不足）的關鍵。

驗證維護計畫是否成功執行的主要方法
- 檢查 Agent Job 歷史記錄每個維護計畫都會在 SQL Server Agent 中對應一個或多個作業 (Job)。在 SSMS 中，展開「SQL Server Agent」>「Jobs」，找到對應的作業，右鍵點擊並選擇「檢視記錄」，即可查看每一次的執行狀態（成功或失敗）與摘要資訊。
- 查詢系統資料表若需要更詳細的執行日誌，可以透過 T-SQL 查詢 msdb 資料庫中的系統資料表來獲取：
  - msdb.dbo.sysmaintplan_log: 用於獲取每次計畫執行的高層級概觀，包含開始/結束時間及整體成功或失敗狀態。
  - msdb.dbo.sysmaintplan_logdetail: 當計畫失敗時，查詢此表。它提供細 granular 的步驟級錯誤訊息，能精確指出是哪個子任務（例如某個特定資料庫的備份）導致了問題，是故障排除的關鍵。
- 配置日誌與通知維護計畫本身提供了完善的記錄選項。在計畫屬性中，可以設定將每次執行的詳細結果記錄到指定的文字檔或 SQL Server 表格中。更重要的是，對於任何生產資料庫，設定作業失敗時的電子郵件通知是強制性要求，而非選項。無聲失敗的備份等於沒有備份。這是我們實現即時故障偵測的主要機制。
在監控過程中，我們可能會發現維護計畫本身的一些限制。下一章節將深入探討這些議題，幫助團隊做出更周全的決策。

常見建議排程範例

任務	頻率建議	時間建議
完整備份	每天 `1` 次	深夜（低流量）
交易紀錄備份	每 `15 分～1 小時`	全日執行
重建或重組索引	每週 `1～2` 次	深夜
更新統計資料	每週 `1` 次	重建索引後
`Check DB Integrity`	每週或每月一次	深夜或維護時段
清除過期備份與記錄	每日或每週一次	任意時間

注意事項與限制
- 維護計畫仰賴 SQL Server Agent，需確保 Agent 啟動中。
- 無法進行太細緻的控制，例如根據特定資料庫屬性條件來排除。
- 設定過多的維護任務在同一個時間，可能導致資源爭用。
- 若有大型資料庫或高頻交易的系統，建議使用 T-SQL 腳本或專業備份軟體進行客製化控管。

重要注意事項與限制

雖然維護計畫是一個非常方便的入門工具，但團隊成員必須清楚認知其固有的限制。了解這些限制有助於我們判斷在何種情境下，應採用更進階、更具彈性的客製化方案。

對 SQL Server Agent 的依賴性: 這是最根本的限制。若 SQL Server Agent 服務因任何原因停止運作，所有已排程的維護計畫都將全部失敗。
缺乏細緻的控制: 維護計畫的邏輯相對簡單，無法進行複雜的條件判斷。例如，我們無法設定「僅當資料庫的索引碎片率超過 30% 時才執行重建」，也缺乏根據伺服器名稱或當前日期等變數，來動態產生備份檔名或目錄路徑的能力，而這在腳本化方案中是實現良好組織的標準做法。
資源爭用的風險: 如果將過多耗費系統資源的任務（例如：資料庫完整備份、重建所有索引、完整性檢查）安排在同一個時間點執行，極有可能導致嚴重的 CPU、I/O 與記憶體資源爭用，進而影響線上服務的效能。
高階場景的適用性: 對於大型資料庫（TB 等級）或高頻交易的關鍵業務系統，維護計畫可能不夠靈活或高效。在這些場景下，強烈建議優先考慮使用客製化的 T-SQL 腳本或整合專業的第三方備份軟體，以實現更精細、更高效能的維護管理。

本文為團隊建立了資料庫可靠性的基準線，掌握這些基礎知識是所有成員的份內職責。您職業發展的下一步，是學會辨識哪些場景需要採用前述更進階的 T-SQL 解決方案。我們的目標是主動管理公司的資料資產，而非被動地應對問題。

任務計劃腳本範例

完整備份腳本範例
完整備份（Full Backup）
mkdor -p c:\SQLBackups\Full

-- 修改變數值以符合你的環境
DECLARE @BackupPath NVARCHAR(200) = N'C:\SQLBackups\Full'
DECLARE @DBName NVARCHAR(200)
 
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb') AND state = 0 AND    recovery_model_desc IN ('FULL', 'SIMPLE', 'BULK_LOGGED')
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @FileName NVARCHAR(400) = @BackupPath + '\' +    @DBName + '_Full_' +
        CONVERT(VARCHAR(20), GETDATE(), 112) + '.bak'
 
    BACKUP DATABASE @DBName TO DISK = @FileName WITH INIT,    COMPRESSION, STATS = 10
 
    FETCH NEXT FROM db_cursor INTO @DBName
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

交易紀錄備份腳本範例
交易紀錄備份（Transaction Log Backup）
mkdor -p c:\SQLBackups\TLog

DECLARE @BackupPath NVARCHAR(200) = N'C:\SQLBackups\TLog'
DECLARE @DBName NVARCHAR(200)
 
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE recovery_model_desc = 'FULL' AND name NOT IN ('tempdb')
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @FileName NVARCHAR(400) = @BackupPath + '\' +    @DBName + '_TLog_' +
        CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE   (CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + '.trn'
 
    BACKUP LOG @DBName TO DISK = @FileName WITH INIT,    COMPRESSION, STATS = 10
 
    FETCH NEXT FROM db_cursor INTO @DBName
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

重建或重組索引腳本範例
重建或重組索引（根據碎片率）

USE master;
DECLARE @DBName NVARCHAR(200)
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb') AND state = 0
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX) =
    'USE [' + @DBName + '];
    DECLARE @table NVARCHAR(300), @schema NVARCHAR(300)
 
    DECLARE index_cursor CURSOR FOR
    SELECT s.name, o.name FROM sys.dm_db_index_physical_stats   (DB_ID(), NULL, NULL, NULL, ''LIMITED'') i
    JOIN sys.objects o ON i.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE i.index_id > 0 AND i.avg_fragmentation_in_percent > 10    AND o.type = ''U''
 
    OPEN index_cursor
    FETCH NEXT FROM index_cursor INTO @schema, @table
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @cmd NVARCHAR(MAX)
        IF EXISTS (
            SELECT 1 FROM sys.dm_db_index_physical_stats(DB_ID   (), OBJECT_ID(@schema + ''.'' + @table), NULL, NULL,    ''LIMITED'')
            WHERE avg_fragmentation_in_percent > 30
        )
            SET @cmd = ''ALTER INDEX ALL ON ['' + @schema + ''].   ['' + @table + ''] REBUILD''
        ELSE
            SET @cmd = ''ALTER INDEX ALL ON ['' + @schema + ''].   ['' + @table + ''] REORGANIZE''
 
        EXEC (@cmd)
        FETCH NEXT FROM index_cursor INTO @schema, @table
    END
 
    CLOSE index_cursor
    DEALLOCATE index_cursor
    '
    EXEC (@SQL)
 
    FETCH NEXT FROM db_cursor INTO @DBName
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

更新統計資料腳本範例
更新統計資料

DECLARE @DBName NVARCHAR(200)
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb') AND state = 0
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX) =
    'USE [' + @DBName + '];
    EXEC sp_updatestats;'
    EXEC (@SQL)
 
    FETCH NEXT FROM db_cursor INTO @DBName
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

Check DB Integrity（DBCC CHECKDB）

DECLARE @DBName NVARCHAR(200)
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb') AND state = 0
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX) =
    'DBCC CHECKDB ([' + @DBName + ']) WITH NO_INFOMSGS,    ALL_ERRORMSGS;'
    EXEC (@SQL)
 
    FETCH NEXT FROM db_cursor INTO @DBName
END
 
CLOSE db_cursor
DEALLOCATE db_cursor

清除過期備份檔案腳本範例
清除過期備份檔案（7天以前）

⚠️ 僅限 Windows 環境，使用 xp_cmdshell（需啟用）或 PowerShell 建議另行管理。
使用 xp_cmdshell 清除備份檔案（如需開啟）：

-- 啟用 xp_cmdshell（必要時）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
 
-- 清除 7 天前備份檔案
EXEC xp_cmdshell 'forfiles /p "C:\SQLBackups\Full" /s /m *.bak /d -7 /c "cmd /c del @path"'
EXEC xp_cmdshell 'forfiles /p "C:\SQLBackups\TLog" /s /m *.trn /d -7 /c "cmd /c del @path"'

建立維護計劃並加入備份資料庫的工作

新增維護計劃
輸入計劃名稱
設定備份參數
點擊子計劃中的排程按鈕，並設定每天晚上 11:30 自動備份。
設計備份工作
選取工具箱中的備份資料庫工作拖曳到子計劃下方的工作區
編輯備份資料庫工作
- 滑鼠右鍵選擇編輯
- 一般
  - 備份類型: 完整
  - 資料庫: demodb
- 目的地
  - 為每個資料庫建立一個備份檔案
    勾選: 為每一個資料庫建立一個子目錄，資料夾: c:\dbbackup
儲存並結束計劃
立即執行備份計劃

建立維護計劃並加入 `T-SQL` 備份指令

新增維護計劃
輸入計劃名稱：備份demodb_TSQL
設定備份參數
點擊子計劃中的排程按鈕，並設定每天晚上 11:30 自動備份。
設計備份工作 (T-SQL 陳述式工作)
選取 工具箱 中的 執行 T-SQL 陳述式工作 拖曳到子計劃下方的 工作區

編輯備份資料庫工作

將以下程式碼貼在程式區中

use [master]
-- 開啟 xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
 
--宣告變數
DECLARE @BackupFolderName nvarchar(250) = ''
DECLARE @DataBaseName nvarchar(250) = ''
DECLARE @JobName nvarchar(250) = ''
DECLARE @KeepDays int = 0
DECLARE @FolderName nvarchar(250) = ''
DECLARE @FileName nvarchar(250) = ''
DECLARE @FileDate nvarchar(250) = ''
DECLARE @Command nvarchar(250) = ''
 
---設定變數預設值
SET @BackupFolderName = 'D:\dbbackup'   --備份資料夾名稱
SET @DataBaseName = 'demodb'   --備份資料庫名稱
SET @JobName = @DataBaseName + ' 資料庫備份工作' --備份工作名稱
SET @KeepDays = 30  --保留備份檔天數
 
--取得日期變數 , 例 2013-01-01 12:30:40.001
SET @FileDate = CONVERT(NVARCHAR(250), GETDATE(), 121)
 
--除去日期變數中的 - : . 符號 , 例 20130101_123040_001
SET @FileDate = REPLACE( @FileDate , '-' , '' )
SET @FileDate = REPLACE( @FileDate , ':' , '' )
SET @FileDate = REPLACE( @FileDate , '.' , '_' )
SET @FileDate = REPLACE( @FileDate , ' ' , '_' )
 
--設定備份目錄,並自動以年、月建立目錄
SET @FolderName = @BackupFolderName
SET @Command = 'mkdir ' + @FolderName
EXEC xp_cmdshell @Command, no_output
 
SET @FolderName = @FolderName + '\' + @DataBaseName
SET @Command = 'mkdir ' + @FolderName
EXEC xp_cmdshell @Command, no_output
 
SET @FolderName = @FolderName + '\' + SUBSTRING(@FileDate , 1 ,    4)
SET @Command = 'mkdir ' + @FolderName
EXEC xp_cmdshell @Command, no_output
 
SET @FolderName = @FolderName + '\' + SUBSTRING(@FileDate , 5 ,    2)
SET @Command = 'mkdir ' + @FolderName
EXEC xp_cmdshell @Command, no_output
 
--設定檔案名稱 , 例    D:\dbbackup\demodb\2013\01\demodb_20130101_123040.bak
SET @FileName = @FolderName + '\' + @DataBaseName + '_'    +SUBSTRING(@FileDate , 1 , 15) + '.bak'
 
--執行備份作業
BACKUP DATABASE @DataBaseName TO DISK = @FileName WITH NOINIT ,    NOUNLOAD , NAME = @JobName , NOSKIP , STATS = 10, NOFORMAT
 
--刪除過期備份檔案
SET @KeepDays *= -1
SET @FileDate = CONVERT(NVARCHAR(250), DATEADD(day , @KeepDays ,    GETDATE()) , 121)
SET @FileDate = REPLACE( @FileDate , '-' , '' )
SET @FileDate = REPLACE( @FileDate , ':' , '' )
SET @FileDate = REPLACE( @FileDate , '.' , '_' )
SET @FileDate = REPLACE( @FileDate , ' ' , '_' )
 
SET @FolderName = @BackupFolderName
SET @FolderName += '\' + @DataBaseName
SET @FolderName += '\' + SUBSTRING(@FileDate , 1 , 4)
SET @FolderName += '\' + SUBSTRING(@FileDate , 5 , 2)
SET @FileName = SUBSTRING(@FileDate , 1 , 8) + '*.bak'
SET @Command = 'del ' + @FolderName + '\' + @DataBaseName + '_'    + @FileName + ' /f /q'
EXEC xp_cmdshell @Command, no_output
 
IF SUBSTRING(@FileDate , 7 , 2) = '01'
BEGIN
	SET @Command = 'rmdir ' + @FolderName + ' /s /q'
	EXEC xp_cmdshell @Command, no_output
END
 
-- 關閉 xp_cmdshell
use [master]
EXEC sp_configure 'show advanced options', 0;
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO

儲存並結束計劃
立即執行備份計劃

Allen 技術筆記

探索

5. 備份計劃

備份計劃-備份資料庫工作

維護計畫的策略性價值

前置準備作業：確保環境整備

核心維護任務詳解

最佳實踐：排程建議與範例

執行結果的監控與驗證

重要注意事項與限制

任務計劃腳本範例

建立維護計劃並加入備份資料庫的工作

建立維護計劃並加入 `T-SQL` 備份指令

關係圖譜

目錄

Allen 技術筆記

探索

5. 備份計劃

備份計劃-備份資料庫工作

維護計畫的策略性價值

前置準備作業：確保環境整備

核心維護任務詳解

最佳實踐：排程建議與範例

執行結果的監控與驗證

重要注意事項與限制

任務計劃腳本範例

建立維護計劃並加入備份資料庫的工作

建立維護計劃並加入 T-SQL 備份指令

關係圖譜

目錄

建立維護計劃並加入 `T-SQL` 備份指令