SQL Server 復原模式深度解析：大量記錄復原模式 (BULK_LOGGED) 原理、優缺點與最佳實務

交易記錄檔暴增是 DBA 常見痛點。本文深度解析 大量記錄復原模式，對比完整復原模式，說明其最小化記錄機制、BCM 頁面追蹤、尾端記錄備份限制，並提供切換策略與風險控制建議。

什麼是大量記錄復原模式？

介於完整復原模式與簡單復原模式之間：

• 大部分交易完整記錄 (同完整模式)。
• 特定大量操作採用最小化記錄，僅記錄頁面配置變更，不記錄逐列變更。
• 支援記錄備份，但包含最小化記錄操作的記錄備份無法執行時間點還原。

最小化記錄的大量操作

• BULK INSERT / bcp / OPENROWSET(BULK...)
• SELECT INTO
• INSERT ... SELECT (搭配 TABLOCK 提示)
• CREATE INDEX / ALTER INDEX REBUILD
• DROP INDEX (叢集索引)
• WRITETEXT / UPDATETEXT (大型值類型)

核心機制：大量變更對應圖 (BCM, Bulk Changed Map)

• 每個資料庫檔案包含 BCM 頁面，追蹤自上次記錄備份以來被大量操作修改的區塊。
• 每個區塊對應 BCM 中 1 個位元：1 = 已被大量操作修改，0 = 未修改。
• 記錄備份時，讀取 BCM 將「已修改區塊的資料頁」完整寫入備份檔，而非逐列記錄。

備份檔案結構示意

[記錄檔頭] [大量操作修改的資料頁 1] [資料頁 2] ... [一般交易記錄]

因此：交易記錄檔可能很小，但記錄備份檔可能很大 (因包含完整資料頁)。

優缺點比較

最佳實務：切換策略

推薦模式：平時完整模式 → 大量操作前切換到大量記錄 → 完成後切回完整模式 → 立即執行記錄備份

-- 1. 大量操作前切換
ALTER DATABASE MyDB SET RECOVERY BULK_LOGGED;

-- 2. 執行大量操作 (如 BULK INSERT, CREATE INDEX, SELECT INTO)
BULK INSERT BigTable FROM 'data.txt' WITH (TABLOCK);

-- 3. 操作完成切回完整模式
ALTER DATABASE MyDB SET RECOVERY FULL;

-- 4. 關鍵：立即執行記錄備份，建立可還原點
BACKUP LOG MyDB TO DISK = '..._log.trn';

關鍵限制：尾端記錄備份

若資料庫處於大量記錄模式且包含最小化記錄交易，執行尾端記錄備份 (BACKUP LOG ... WITH NORECOVERY) 時，所有被大量操作修改的資料檔案必須線上可存取。若資料檔受損離線，將無法備份尾端記錄，導致該時間點後的所有交易永久遺失。

英文官方文件重點摘要

The bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. ... If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

總結

大量記錄復原模式是大量資料載入、索引重建等維護作業的「效能加速器」，但引入「無法時間點還原」與「尾端備份依賴資料檔」兩大風險。嚴格遵循「切換→操作→切回→備份」四步驟，並確保資料檔高可用性，才能安全享受效能紅利。