SQL Server 大型事務日誌備份還原深度解析：為何還原比備份慢及鏡像/日誌傳送選型指南

核心疑問：同樣 62GB，還原為何遠慢於備份？

備份兩步走

1. 讀取數據文件所有已分配頁 → 寫入備份設備。
2. 讀取必要事務日誌（保證恢復一致性點） → 寫入備份設備。

還原四步走

1. 創建文件： 若文件不存在，分配空間並零初始化（SQL 2000 必須，2005+ 默認）。
   → 對策： 不刪除現有文件覆蓋還原，或啟用即時文件初始化（授予服務賬號 SE_MANAGE_VOLUME_NAME）。
2. 數據/日誌寫入： 讀備份 → 寫 MDF/LDF，耗時與備份步驟 1/2 相近。
3. Redo（重做）： 正向應用日誌，確保已提交事務持久化。
4. Undo（回滾）： 逆向撤銷未提交事務，保證數據庫事務一致性。
   → 步驟 3/4 耗時取決於活躍事務日誌量，長事務會顯著拖慢還原。

鏡像 vs 日誌傳送：索引重建場景的網絡負載真相

常見誤區

「鏡像發送重建命令而非日誌，BULK_LOGGED 模式下鏡像優於日誌傳送。」

事實真相

• 鏡像機制： 發送實際事務日誌記錄，鏡像端重播，無命令攔截/轉換。
• 恢復模式限制： 鏡像僅支持 FULL，索引重建全量記錄 → 大量日誌、大日誌文件、高帶寬佔用。
• 日誌傳送靈活性： 支持 FULL/BULK_LOGGED。BULK_LOGGED 下重建極少日誌，但下次日誌備份仍包含變更區塊，備份大小與 FULL 幾乎相同。
• 結論： 兩者傳輸數據量幾乎相同，差別僅在連續流 vs 批次傳輸。

選型決策矩陣

最佳實踐建議

1. 啟用即時文件初始化，大幅縮短還原步驟 1。
2. 避免長事務跨越備份窗口，減少 Redo/Undo 開銷。
3. 鏡像 + 日誌傳送混合部署： 鏡像保 HA，日誌傳送備異地 DR/報表。
4. 監控日誌佔用： DBCC SQLPERF(LOGSPACE)，預警防止日誌填滿。

1. 上一篇文章： SQL Server COALESCE 函數實戰：優雅替代巢狀 CASE WHEN 的多列非空取值神器
2. 下一篇文章： SQL Server T-SQL 編程入門：變量、流程控制與實戰範例全攻略

關於SqlServer

MS SQL Server 觸發器應用實戰：INSERT/UPDATE/DELETE 與巢狀觸發器

本節透過大量實例，展示三類 DML 觸發器的寫法與關鍵技巧，

SQL Server 2005 安裝與新功能圖解：VS 2005 衝突解決與管理工具新面貌

安裝衝突：VS 2005 Beta 1 與 SQL 2005

Win7 32/64位系統安裝 SQL Server 2005 及 SP3 补丁完整图文教程

安装前准备 本教程适用于 Windows 7 32位/64位

T-SQL 程式設計基礎：變數、流程控制、資料類型轉換與字串操作完整入門

1. 變數宣告與指派 區域變數 前綴 @，批次/預存程序範