2026 iThome 鐵人賽

DAY 14

自我挑戰組

SQL Server 基礎&調教系列第 14 篇

【基礎】 14.備份&還原

18th鐵人賽

SQLMASTER

2026-08-14 21:08:23

74 瀏覽

分享至

這是一個非常重要的日常維護一部分，整個策略都會影響整體的安全性、效能，所以這裡會很深入的說明這整個機制。

而在真正了解復原模式、備份之前，必須要先了解什麼是交易?

交易

何謂交易

在 SQL Server 中，交易是一組必須被視為單一工作單位的資料操作，也就是說這一組操作，要馬全部成功，要馬全部失敗，不允許成功一半、失敗一半。

BEGIN TRAN;

UPDATE Account
SET Balance = Balance - 1000
WHERE AccountID = 1;

UPDATE Account
SET Balance = Balance + 1000
WHERE AccountID = 2;

COMMIT;

這裡有兩個 Update

第一個是 A帳號扣 1000

第二個是 B帳號 + 1000

那我們當然不能接受 A帳號有扣錢但 B帳號沒加錢，反過來的話也不能接受。
而這一整個操作就是一個單位，這個單位就是交易。

交易核心意義

交易要解決的問題是 : 資料在任何時間點，都不能停留在一個邏輯錯誤的中間狀態。

這是資料庫引擎對資料一致性的承諾

就像第一點舉例的例子，交易就是在保證資料永遠都是一致。

SQL Server 交易是靠 ldf 來保證。

交易中有一個關鍵字 commit

這個關鍵字應當理解為 : 這筆交易紀錄已經寫入 ldf，但注意這不代表已經寫入 mdf。

commit 存在的意義是 : 交易結果成立，SQL Server 必須保證他不會消失，沒有 commit 會消失的原因是因為，log record 在 commit 以前都存在記憶體，commit 以後才會寫入 ldf 確保存續。

ACID

A = Atomicity 原子性
C = Consistency 一致性
I = Isolation 隔離性
D = Durability 持久性

Atomicity : 一個交易所有操作，要馬全部完成，要馬全部不完成。

Consistency : 交易執行錢，資料庫是合法狀態，結束後亦然。

Isolation : 多交易同時執行，彼此不能用不正確的方式干擾。

例如 :
兩個人同時購買同一商品
交易 A : 庫存扣 1
交易 B : 庫存扣 1
但實際上庫存本身只剩下 1
如果沒有隔離性，兩個交易都成功，就會造成其實庫存不夠的問題
所以 SQL Server 會透過

Lock
Latch
Isolation Level
Row Versioning

來控制這個問題

Durability : 一旦交易 commit 成功，即使 SQL Server 馬上停機，這筆交易也不能消失。

SQL Server 必須先確保 commit 相關的 log 紀錄都已經寫到 ldf 上了
只要已經成功寫入硬碟，即使 page 還沒寫回 mdf，SQL Server 重啟後仍然可以根據 log 把資料補回來。

LSN

全名是 Log Sequence Number。

在 SQL Server 交易紀錄裡，每一筆交易紀錄，都有一個遞增的識別值，這識別值就是 LSN。

例如 :
LSN 100：交易 T1 開始
LSN 110：T1 修改 AccountID = 1
LSN 120：T1 修改 AccountID = 2
LSN 130：T1 COMMIT
LSN 140：交易 T2 開始
LSN 150：T2 修改 OrderID = 10
LSN 160：T2 ROLLBACK

無論何種備份，本質上都跟 LSN 有關。

DML 順序

BEGIN TRAN;

UPDATE dbo.Account
SET Balance = 900
WHERE AccountID = 1;

COMMIT;

交易發生，就會直接先產生 log record，此時放在記憶體裡 log cache。
讀取資料放在 buffer pool。
開始 UPDATE 產生 update 的 log record。
commit 產生 commit 的 log record，然後 SQL Server 必須確保這個 log record flush 到 ldf，使用者才會收到 commit 成功。
至於 MDF 的寫入時間不一定，有可能在 checkpoint、有可能在 lazy writer，也有可能還沒寫入就 crash。

Dirty Page

如果 page 還在 buffer pool 中被修改，但沒有寫回 mdf，這個 page 此時就是 dirty page。

因為他跟 mdf 裡面紀錄的不一樣，所以被稱為 dirty。

`交易紀錄儲存的路徑`

-- 假設 ID =1 的 Balance = 1000 
-- 然後現在要把它改成 900
BEGIN TRAN;

UPDATE dbo.Account
SET Balance = 900
WHERE AccountID = 1;

COMMIT;

一樣是這個例子

在 DML 順序的裡面已經大致知道整個交易的過程，接下來是

整個完整的交易紀錄，從開始到寫進 LDF 的路徑。

在這個案例中，交易一路開始跑，跑到 UPDATE 之後，現在的狀態是

Buffer Pool：( 記憶體 )
資料頁被改成 900，變成 dirty page

Log Buffer：( 記憶體 )
產生 UPDATE 交易紀錄、BEGIN TRAN 交易紀錄

LDF：( 硬碟 )
不一定已經有這筆 UPDATE 交易紀錄

不一定的原因，等等會說明。

接下來會有兩條路，第一條 : 還沒 Flush 到 LDF 就 Crash

根據上面的狀態來看現在有 :

dirty page、UPDATE 的交易紀錄在記憶體裡

LDF 現在沒有東西

MDF 原本的舊資料還是原始的 1000

那我資料庫 crash 之後，MDF 還是 1000，LDF 也不會有 UPDATE 成 900 的紀錄，所以整體還會是 1000。

那這樣沒有問題，這就是前面說 LDF 不一定會有紀錄的原因，我有可能在這裡就 Crash。

第二條路 : 在整個 Crash 之前，UPDATE 交易紀錄就已經被 Flush LDF 上

首先，並不是一定要 commit 才會把交易紀錄寫到 LDF 上，commit 發生時，SQL Server 必須確保這個交易到 commit 為止所需的所有 log records 都已經 flush 到 LDF，包括前面的 UPDATE log record 以及最後的 COMMIT log record。但前面的 UPDATE log record 他可以自己先跑去寫到 LDF 上，至於他什麼時候跑去寫到 LDF 等等會說。

那這條路線就會變成現在 :
LDF 上面有 UPDATE 的紀錄

MDF 可能是 1000 可能是 900

但是 LDF 沒有 Commit 的紀錄，他只有 update 的

那 Crash 結束之後，SQL Server 會去看這些東西，當她看到，這裡沒有 commit 紀錄，他就會判斷，這是一筆未完成交易，不能讓它的結果保留在資料庫中，所以要 undo 回去。

undo 的意思是，不該成功的確保他不成功
因為這裡沒有 commit 的紀錄，所以認定這是不該成功的交易，那就要返回去確保他沒有成功。

這就是兩種可能，造成不一定的原因。

那為什麼第一條路 mdf 一定是原始的 1000

而第二條路就改寫成可能是 1000，可能是 900 ?

這是因為 SQL Server 有一個機制叫做 Write-Ahead Logging ( WAL )

如果想要把資料寫到 MDF，哪他一定要有相關的交易紀錄寫進 LDF 在前。

所以在第一條路中，UPDATE 的紀錄根本還沒寫到 LDF，那 MDF 自然不可能變成 900。

但是第二條路，UPDATE 的交易紀錄已經寫到 LDF 了，那麼 MDF 是有可能可以去改的，他不需要等到 Commit 才可以去改，因為原則就是那樣，只要有交易紀錄寫進 LDF 在前，那 MDF 就可以去改。

那為什麼是可能可能，則是因為要寫進 MDF 需要 checkpoint 或 lazy writer，這個在第二條路的路徑中不一定已經發生。

那 UPDATE 交易紀錄何時會寫到 LDF?

COMMIT 發生時，因為都要COMMIT 了，那就是一定要全部紀錄都寫進去
Dirty page 要寫進 MDF 時，因為 WAL 機制，要寫進 MDF 就是要先寫 LDF。
Log Buffer 滿了，因為這紀錄本來就是存在記憶體，滿了就要循環所以此時也進 LDF 也很合理。
Checkpoint / 其他需要推進 recorvery 狀態的行為

那其實還有第三條路

前面兩條路一條是
交易紀錄沒有寫進 LDF 就 Crash

另一條是
交易紀錄有寫進 LDF 但是沒有 commit 就 Crash

現在要說的第三條就是
交易紀錄有寫進 LDF 而且也已經成功 commit 但是還沒有寫進 MDF 就 Crash

這條路比較好理解，因為前面交易都已經確定成功了，那 Crash 之後，SQL Server 一樣會檢查這些交易，當他看到交易已經commit 了，但是尚未完整反映到 MDF，他就會執行 redo。

redo 意思就是，該成功的就要成功，而該成功的定義就是 commit 有沒有完成。

備份的基礎-復原模式

這會依據使用的復原模式不同，在 SQL Server 中可以執行三種類型的備份 : 完整備份、差異備份、交易紀錄備份。

接下來先探討復原模式

SIMPLE Recovery Model

當資料庫設定成 SIMPLE 復原模式的時候，交易紀錄會在每一次 checkpoint 作業之後被截斷，更準確地說，是交易紀錄中那些已經不再需要的 VLFs 會被截斷。

不再需要用來 crash recovery、rollback active transaction、replication / CDC / AG / mirroring 等功能的 VLFs，就叫做不再需要的 VLFs。

Checkpoint

SQL Server 定期把 dirty pages 寫回實體資料擋，並在交易紀錄中標記一個復原起點的一個機制。

首先，我們在做 DML 的時候，並不是每一次都直接立刻改到硬碟上的 .mdf。

通常會先把 page 從硬碟讀到 buffer pool
使用者修改資料
sql srever 把變更紀錄到交易記錄檔
記憶體中的 page 被修改，變成 dirty page
之後再由 checkpoint 寫回資料檔。

而復原起點就是告訴 SQL Server ，如果之後發生當機，從這個點附近開始復原就可以了。

如果一個資料庫連續運作 10 小時，而且沒有使用 checkpoint ，然後這中間又一直進行 DML 的話；

接下來突然發生斷電，SQL Server 重起的時候，會需要從 10 個小時以前的 LOG 開始重做交易，復原時間就會很長；

但是有 checkpoint 的話，代表 SQL Server 已經把一批 dirty pages 寫回資料檔，並在 log 中留下 checkpoint 資訊。這樣 crash recovery 時，就不需要從很久以前的 log 開始處理，而是可以從 checkpoint 相關的位置附近開始，復原時間通常會縮短。

所以 checkpoint 的核心目的是 : 控制 crash 後復原的時間。

而這意味通常我們不需要特別去管交易紀錄，但這也代表我們無法進行交易紀錄備份。

因此在 SIMPLE 復原模式下，交易會採用最小紀錄，所以在某些作業中可以提升效能

BULK INSERT
SELECT INTO
對使用 .WRITE 子句的大型資料型別執行 UPDATE 陳述式
WRITETEXT
UPDATETEXT
建立索引
重建索引

SMIPLE 的主要缺點是，無法還原到特定時間點；你只能還原到完整備份結束時的狀態。這個缺點會因為完整備份可能對效能造成影響而更加明顯。

意思是因為完整備份要消耗效能，所以策略上完整備份頻率會更少，然後就會造成選 SIMPLE 模式的話，還原的時間點會更遠以前。

另一個缺點是，SMIPLE 與某些 HA/DR 功能不相容

AlwaysOn 可用性群組
資料庫鏡像
紀錄傳送

因此，在正式環境中，SIMPLE 我只會推薦用於大型資料倉儲類型的應用程式，因為這類環境通常會在離峰時間執行 ETL 載入，然後再尖峰其餘時間提供唯讀的報表查詢。

因為通常 ETL 載入之後，會做一次完整備份，之後就是唯讀狀態，這樣資料就不會遺失。

非正式環境 SIMPLE 也很常見。

Full Recovery Model

這個模式跟 SIMPLE 的 CHECKPOINT 會有一個差別，

SIMPLE 的 CHECKPOINT 會截斷 VLFs，Full 的不會。

這意味著，如果使用 Full 模式，那就必須去排成交易紀錄備份，並且讓他頻繁的執行。如果不這麼做，不只會讓資料庫發生故障時面臨無法復原的風險，也代表 LDF 檔會持續成長，值到空間耗盡並拋出 9002 錯誤。

我在壓縮的時候就有講過，在同樣的業務環境與備份策略下，你必須去接受交易紀錄就是會這麼大的原因就是如此，因為備份不充分，LDF 在 FULL 模式下只能繼續長。

FULL 模式的主要優點是，他可以進行時間點復原，而不是像 SIMPLE 一樣只能點對點復原。

還有，FULL 模式與所有 SQL Server 功能相容。對正式環境來說，通常是最佳的復原模式選擇。

如果從 SIMPLE 切換到 FULL，在第一次交易紀錄備份之前，資料庫實際上還不算真正處於 FULL 模式。所以要記得先做一次交易紀錄備份。

BULK_LOGGED Recovery Model

這是設計用來當執行大量匯入作業時，短時間暫時使用的復原模式。

BULK INSERT

這是把外部檔案，例如 CSV、TXT，大量倒入 SQL Server table 裡的東西

一次性大量的寫入，跟一筆一筆 insert 效率相差甚鉅。

BULK INSERT 是大量資料載入，如果每一筆都完整寫交易紀錄，那 LOG 會暴增，所以在某些條件下，SQL Server 可以對 BULK INSERT 使用最小紀錄。

也就是 : 不逐筆完整記錄每一列 INSERT 細節，而是用比較粗略的方式記錄大量載入的 PAGE / EXTENT 變更。

最小紀錄跟 SIMPLE 復原模式，是兩件完全沒關聯的事情

項目	SIMPLE	Minimal Logging
本質	復原模式	寫 log 的方式
作用範圍	整個 database	某些特定操作
目的	控制 log 保留與還原能力	減少大量作業產生的 log
是否能 log backup	不行	看 recovery model
是否等於不寫 log	不是	不是

概念是：資料庫平常使用 FULL 復原模式，然後在大量匯入作業開始前，暫時切換到 BULK_LOGGED 復原模式；等匯入完成後，再切回 FULL 復原模式。

這樣會帶來效能上的好處，也可以避免交易記錄被填滿，因為大量匯入作業在符合條件時會採用最小記錄。

在切換到 BULE_LOGGED 復原模式之前，以及切回 FULL 復原模式之後，最佳做法都是立即執行一次交易記錄備份。

原因是：如果某個交易記錄備份中包含最小記錄交易，你就不能使用該交易記錄備份進行時間點復原。

基於同樣原因，在切換到 BULK_LOGGED 復原模式之前，最佳做法也是先讓應用程式進入安全狀態。通常做法是停用除了執行大量匯入的 login，以及系統管理員 login 以外的其他 login，以確保不會有其他資料修改發生。

你也應該確認正在匯入的資料，可以透過還原以外的其他方式重新取得。遵守這些規則，可以降低災難發生時資料遺失的風險。

但是，這並不意為用這種方式，寫入速度就一定會比 Full 更快。我看過很多人都認為，在大量寫入的時候，使用 BULK_LOGGED，因為他紀錄的 LOG 資訊更少，所以整體寫進磁碟的東西更少，那速度他們就認為比 FULL 還要快。

這是不一定的。

假設現在有一個 100GB 的 CSV 要匯入 SQL Server；所以就下了這個指令

BULK INSERT dbo.BigImport
FROM 'D:\Import\data.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
);

然後再假設現在資料庫是 FULL 模式，接下來看會發生什麼
1. 讀取 CSV
2. 寫入 PAGE 到 data file
3. 大量寫入交易紀錄 < 大量成本發生在此
4. commit 時 flush log
5. 之後去備份交易紀錄
那如果資料庫是 BULK_LOGGED 呢? ( 依照這情況，符合最小交易條件 )
1. 讀取 CSV
2. 寫入 PAGE 到 data file
3. 寫交易紀錄，但是這裡只會紀錄配置了那些 extent、page，哪些 allocation map 被修改。
4. commit 時 flush log + 強制 flush data page
5. 之後去備份交易紀錄
由此可看出，大部分都一樣，只有在怎麼紀錄交易紀錄上、跟 flust data 的時機點，這兩種模式有重大差異

FULL：寫很多 log
BULK_LOGGED：寫比較少 log

FULL : 可以等 CHECKPOINT 再慢慢把實際 data 從記憶體寫回實體硬碟
BULK_LOGGED : 只要 BULK INSERT 一結束，強制所有 data 馬上從記憶體直接寫回實體硬碟。

所以按照上面來看，那 BULK_LOGGED 一定比較快，因為他寫的東西更少。

`但問題在後面`

最小紀錄交易有一個問題，就是他的紀錄裡面，沒有完整記錄每一筆資料怎麼被插入的。

那以後還原的時候，SQL Server 要怎麼重建這批資料 ?

如果 log 裡面沒有完整 row-level detail，那就不能只單靠 log records 去重播出完整的資料。

所以這時候 SQL Server 必須補一樣東西 :

在交易紀錄備份的時候，把最小紀錄交易修改過的 data extents 一起備份進去。

按照現在的案例 100gb資料，約等於 1638400 個 extents，那意思就是，SQL Server 在做交易紀錄備份的時候要把這 100GB / 1638400個 extents 也備份起來。

所以這兩種模式下的第五步 : 備份交易紀錄，差異就會開始顯現

FULL 模式下的交易紀錄備份，主要去備份交易紀錄，因為他的紀錄完整，已經足夠讓還原的時候依照這個紀錄去重播。

但 BULK_LOGGED 呢? 當他去做交易紀錄備份的時候，他如果只備份交易紀錄，會造成她只備份了一堆最小紀錄，紀錄上只寫影響那些 EXTENT、PAGE，靠這個東西沒有辦法還原資料庫，所以他除了備份他的最小紀錄外，他還要備份整個受影響的 EXTENT 。

還有另一個問題 BULK_LOGGED 強制 flush data pages 造成的影響

FULL 模式下，因為 SQL Server 有完整的 log 紀錄
所以即使資料 page 還沒有寫回 data file，SQL Server 也可以靠 log 去 redo。
因此 page 可以先留在 buffer pool，等 checkpoint 慢慢寫回。

但是 BULK_LOGGED + 最小紀錄交易時，log 沒有完整的 row-level detail。
所以 SQL Server 不能完全依賴 log 來重建資料頁細節。
因此他必須確保，那些被最小紀錄交易的資料 page 本身，已經實際落到 data file上了。
否則如果有問題，他沒有辦法還原。
所以才會強制 flush 這些 pages。
但是這會造成 : 資料擋寫入 I/O 集中爆發。

那我們都知道，任何硬碟的讀取速度是有上限的，無論是hdd、ssd、NVMe，而且除了這個 BULK INSERT 以外，SQL Server 可能也會有其他操作 ( 這也是我在上面有說如果要進行 BULK INSERT 建議讓應用程式處於安全狀態的其中一個原因 )，更遑論這個 I/O 甚至可能不只 SQL Server 在用，舉例來說還有作業系統…等等

這種狀況下，如果因為 BULK_LOGGED 導致短時間承受這種 I/O 爆發，會把整個磁碟效能榨乾，然後就會開始有各種 Wait type 了，IO_COMPLETION、ASYNC_IO_COMPLETION....等等。

所以，基於
第一 : 雖然寫入量少，但是備份時間會拉長，整體時間拉長。

第二 : 即使不在意備份時間，BULK 結束後的 I/O 的集中爆發，也會拖慢整個資料庫速度。

這兩個原因，不能直接了當的斷定，大量匯入時用 BULK_LOGGED 一定可以帶來正面的效能影響。

真正能判斷哪一種復原模式可以帶來更好效能的關鍵瓶頸是 : log 磁碟的 I/O 效能。

如果在 FULL 模式下，大量匯入的主要瓶頸是 transaction log 寫入，例如 log disk 很慢、WRITELOG wait 很高、log file 快速成長，甚至有 log 空間不足的風險，那 BULK_LOGGED 就很可能會有優勢。因為在符合 minimal logging 條件時，它可以大幅減少寫入 transaction log 的量，降低 log disk I/O 壓力。

但如果 transaction log 已經放在低延遲、高吞吐的儲存設備上，例如獨立 SSD、NVMe、RAID 10，或本身 log I/O 並不是瓶頸，那 BULK_LOGGED 的優勢就會下降。這時候瓶頸可能反而落在 data file I/O、bulk operation 結束後的 data page flush、transaction log backup 時備份 affected extents，或 backup 目標，例如 NAS 網路吞吐與延遲。

因此，BULK_LOGGED 不應該被理解成「大量匯入一定比較快」的模式。它真正的價值是：在需要保留 log chain 的前提下，降低大量作業對 transaction log 的壓力。至於整體速度是否比 FULL 更快，必須看省下的 log I/O，是否大於額外產生的 data page flush 與 log backup affected extents 成本。

除非你有非常快速的 I/O 子系統，否則在大量匯入時，BULK_LOGGED 復原模式不一定會比 FULL 復原模式更快。
原因是 BULK_LOGGED 復原模式會強制那些以最小記錄方式更新過的資料頁，在作業完成後立刻 flush 到磁碟，而不是等待 checkpoint 作業。
SQL Server 會透過 bitmap pages 追蹤這些資料頁，這些 bitmap pages 稱為 ML pages，也就是 minimally logged pages。每 64,000 個 extents 會有一個 ML page，並使用旗標來表示對應 extent 區塊中的每一個 extent，是否包含最小記錄的資料頁。

三種 Model 比較

復原模式的核心考量決策是 :

交易紀錄要保留多久?
要不要做交易紀錄備份?
需要點對點還原還是點對時間還原?
大量作業是否能用最小紀錄?

SIMPLE

首先在這個模式下，交易紀錄的用途，是拿來支援.交易的時候需要 rollback 或是資料庫 crash 時，需要 redo、undo的。
所以當發生 checkpoint 後，只要某個紀錄已經不再用於上述幾種用途，那麼對應的 VLFs 就會被標記成可重寫。

而這同時也代表 SIMPLE 不支援.交易紀錄備份。

所以在此模式下，他只支援點對點復原 :

01 : 00 交易
02 : 00 交易
03 : 00 完整備份
04 : 00 交易 — 此處理應做交易紀錄備份，但是 SIMPLE 沒有辦法做，你要馬在這裡也做完整備份，要馬就是承受如果之後 CRASH 了，這裡資料無法復原的風險；但是完整備份非常消耗效能，沒有人會一天到晚做完整備份。
05 : 00 CRASH ( 需要 Backup Restore的程度 )

意思就是，上述時間表，如果是在 SIMPLE 模式下，只有可能還原到 3點的那個備份，而 3點後到 5 點之間的紀錄，會全部消失。

傻眼支援.交易，裡面有禁止字元我寫完整篇四萬字才發現一個一個查 ==

Full

在這個模式下，SQL Server 會完整保留交易紀錄”鏈”，他是保留鏈，雖然表面上看起來是都在保留交易紀錄，可是要把他想成他是在保留一整串鏈，才能明白他在做什麼。

意思就是，在 SIMPLE 中，只要 checkpoint 後，除了還有特定用途以外的 log，都會被標記成可重寫，但是，在 FULL 中，checkpoint 後，就算他已經沒有 SIMPLE 那些特定用途了，他還是不會因為已經 checkpoint 了，就去標記 VLFs 可重寫。

他會一直等到做了，交易紀錄備份之後，才把那些沒有用途的 log 去重寫，而對於 SIMPLE 來說沒有用途的紀錄，其實在 FULL 模式裡面她還有別的用途。

這是為了確保 FULL 模式可以進行點對時間的還原。

01 : 00 交易
02 : 00 交易
03 : 00 完整備份
04 : 00 交易 — 交易紀錄備份
05 : 00 交易 — 交易紀錄備份
05 : 10 CRSH

按上述時間表，在 FULL 模式下，我理想狀況下可以還原到 05:09:59，也就是 CRASH 前一秒。
因為我有完整的交易鏈。

完整備份 + 3~4 點的交易紀錄備份 + 4~5 點的交易紀錄備份 + 05 : 00 ~ 05 : 10 不會被標記可重寫的那些紀錄。

最後的這十分鐘記錄，就是在 SIMPLE 模式沒有用途，應該要可重寫，但是在 FULL 模式有用途的地方。
這就是一整條交易鏈，資料可以保證都保留，所以我才推薦正式環境中使用。

Bulk_Logged

這個要去把他理解成是 FULL 模式的特殊變體。

因為他跟 FULL 一樣可以做交易紀錄備份，也可以保留整個交易鏈。

但是在特定大量作業符合條件的時候，SQL Server 會使用最小交易紀錄，去減少 log 的寫入量。

所以 BULK_LOGGED 模式的主要目的，是去控制當有大量寫入時的 log 大小。

原本是 full 模式
01 : 00 交易
02 : 00 交易
03 : 00 完整備份
04 : 00 交易 — 交易紀錄備份
04 : 30 切換成 Bulk_Logged 模式
04 : 35 開始 BULK INSERT
04 : 50 BULK 結束
05 : 00 切換回 FULL 模式
05 : 10 交易紀錄備份
05 : 20 CRSH

判斷哪一個時間點沒有辦法精準還原地關鍵在於 LOG BAKUP 在哪

在此案例中，交易紀錄備份之後到下一次交易紀錄備份之前的這段時間，無法還原到精確時間點

例如沒辦法還原到 04:45 那個當下，因為那個時候已經切換成 BULK_LOGGED 了。

所以我可以還原到

3點
3點 + 3~4 點
3點 + 3~4點 + 4點 ~ 5 : 10 如果可以做 tail-log backup 就再 + 5:10~5:20
但是沒有辦法 3點 + 3~4點 + 4點 ~ 4點45

備份類型

總共有三種 :

完整備份、差異備份、交易紀錄備份

完整備份

任何復原模式都可以進行完整備份

當執行備份命令時，SQL Server 會先發出一個 CHECKPOINT，這會使所有 dirty pages 被寫入磁碟。接著，SQL Server 會備份資料庫中的每一個 page，這個階段稱為 data read phase。

最後，SQL Server 會備份足夠的 transaction log，這個階段稱為 log read phase，以確保交易一致性。

這可以確保你能夠將資料庫還原到備份中最新的時間點，包括那些在備份的 data read phase 期間提交的交易。

差異備份

差異備份會備份資料庫中，自上一次完整備份以來所有已被修改過的 page。

SQL Server 會使用稱為 DIFF pages 的 bitmap pages 來追蹤這些 page。DIFF pages 每 64,000 個 extents 會出現一次，並使用旗標來表示其對應 extent 區塊中的每一個 extent，是否包含自上一次完整備份以來已被更新過的 page。

交易紀錄備份

在復原模式中，提到過很多次交易紀錄備份

所以現在已經知道，交易紀錄備份，只有在 FULL 或 BULK_LOGGED 下可以執行

當在 FULL 復原模式下執行交易記錄備份時，它會備份自上一次 Log Backup 之後尚未備份的 transaction log records，並維持連續的 Log Backup Chain。

當在 BULK_LOGGED 復原模式下執行交易記錄備份時，它也會備份任何包含最小記錄交易的 pages。

當備份完成後，SQL Server 會截斷 transaction log 中的 VLFs，直到遇到第一個 active VLF 為止。

交易記錄備份對支援 OLTP（online transaction processing，線上交易處理）的資料庫特別重要，因為它們允許資料庫進行時間點復原，還原到災難發生前的那一刻。

交易記錄備份也是資源消耗最少的備份類型，這表示你可以比完整備份或差異備份更頻繁地執行交易記錄備份，而不會對資料庫效能造成明顯影響。

備份媒體

可以備份到硬碟、URL

與備份媒體相關的術語包含：backup devices、logical backup devices、media sets、media families，以及 backup sets。

Backup Devices

備份裝置可以是磁碟上的實體檔案，也可以是雲端儲存體。

舊版 SQL Server 只支援將 Windows Azure Blob Storage 作為 URL 目標，但 SQL Server 2022 也支援 AWS 的 S3 儲存體。

當備份裝置是磁碟時，該磁碟可以位於伺服器本機，也可以位於共享路徑上。

一個 media set 最多可以包含 64 個 backup devices，資料可以分散寫入多個 backup devices，也可以進行鏡像。

意思是，一個備份最多可以分散成64個檔案，寫進64個路徑。

將備份分散寫入多個裝置，對大型資料庫很有幫助，因為這可以讓你把每個裝置放在不同的磁碟陣列上，以提高吞吐量。不過，這也會帶來管理上的挑戰；如果 stripe 中其中一個磁碟裝置無法使用，你就無法還原備份。

所可以透過使用 mirror 來降低這個風險。使用 mirror 時，每個裝置的內容都會被複製到另一個額外的裝置，以提供備援。如果 media set 中有一個 backup device 被鏡像，那麼 media set 中的所有 devices 都必須被鏡像。

這個機制跟 RAID 10 很像。

每一個 backup device，或每一組 mirrored backup devices，都稱為一個 media family。每個 device 最多可以有四個 mirrors。

最多有四組鏡像，也就是總共五組備份資料

一個 media set 中的所有 backup devices 必須全部都是磁碟，或全部都是 URL。如果使用鏡像，mirror devices 必須具有相似的屬性；否則會拋出錯誤。因此，Microsoft 建議 mirror 使用相同品牌與型號的裝置。

也可以建立 logical backup devices，用來抽象化實體 backup device。使用 logical devices 可以簡化管理，特別是當你計畫在同一個實體位置使用大量 backup devices 時。

這東沒什麼，就只是去幫妳的路徑設定一個別名
例如原本應該要

BACKUP DATABASE TEST
TO DISK = 'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak';

但是經過一次設定之後

EXEC sp_addumpdevice 
    @devtype = 'disk',
    @logicalname = 'TEST',
    @physicalname = 'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak';
GO

之後就可以改寫成，就這樣而已

BACKUP DATABASE TEST
TO TEST_Backup_Device;

Media Sets

這個算是一個容器，用來包容 backup device的

他是會存一些 header 資訊，但我覺得沒有很重要

Backup Sets

每一次備份寫入 media set 時，這一次備份就稱為一個 backup set。

備份策略選擇

備份策略，永遠應該以應用程式的 RTO 和 RPO 需求為基礎。

如果妳 RPO 是 60 分鐘，但是每天只做一次備份，那妳永遠無法達成這個目標。

策略一、只做完整備份

這是彈性最低的策略。

但是，如果資料庫很少更新，而且有固定的備份時間窗口，並且這個時間窗口足夠長，可以完成一次完整備份，那麼這可能是一個合適的策略。

還有系統資料庫也蠻適合這個策略。

但是你要想清楚，只使用完整備份的策略，也會限制你在還原時的彈性。

如果只做完整備份，那麼唯一的還原選項，就是從最後一次完整備份的時間點來還原資料庫。

這會造成兩個問題。

第一個問題是，如果每天晚上午夜執行夜間備份，而資料庫在 23:00 發生損毀，那麼你會遺失 23 小時的資料異動。

第二個問題是，如果使用者在 23:00 不小心 truncate 掉一張資料表，那麼這個資料庫最早能還原的時間點，也是前一天晚上的午夜。

在這個情境下，同樣地，這次事件的 RPO 是 23 小時，也就是代表會遺失 23 小時的資料異動。

策略二、完整備份 + 交易記錄備份

這個策略必須在 FULL 或 BULK_LOGGED 模式下才能進行。

因為交易紀錄備份速度更快，使用資源更少，表示我們可以更頻繁的備份交易紀錄。

這種策略適合用於整天都會持續更新的資料庫，而且它也提供更有彈性的還原方式，因為你可以把資料庫還原到災難發生前的某一個時間點。

使用這個策略要考慮的是 PRO 跟 RTO。

PRO :
如果只能接受 60 分鐘的資料損失，那麼就安排交易紀錄備份每 60 分鐘執行一次。

RTO :
這就要考慮完整備份的時機點，因為如果一個禮拜做一次完整備份，然後剩下的都做交易紀錄備份，就會發生，接近下一次完整備份的時候，資料庫 CRASH 了，現在要還原會是，完整備份 + 一堆交易紀錄備份，時間很可能就超過應用程式可以忍受的停機時間。

策略三、完整備份 + 交易記錄備份 + 差異備份

在策略二中，RTO 會因為有太多交易紀錄備份檔，導致還原的時候非常慢，加入差異備份就可以解決這個問題。

因為差異備份是累積的，交易紀錄備份是增量的

禮拜一完整備份
禮拜二差異備份
禮拜三差異備份
禮拜四差異備份
禮拜五 crash

然後每十五分鐘做一次交易紀錄備份

這時後還原的路徑就會是完整備份 + 禮拜四的差異備份 + 從禮拜四開始一路到禮拜五的交易紀錄備份 + 最後來不及交易紀錄備份的尾端交易紀錄備份 ( 這個要看情況，不一定每次災難發生都可以拿到 )

用這樣子去還原，就不會遇到像策略二那樣，會有幾百個交易紀錄備份要還原然後等超久。

策略四、檔案群組備份

對於一個非常大型的資料庫來說，可能無法找到一個足夠長的維護時間，來對整個資料庫執行完整備份。

這種情況下，就需要把資料都分散到不同的 filegroup，然後每隔一晚備份其中一些 filegroup，用這種方式達到完整備份。

然後如果遇到需要還原的情境時，可以只還原包含損毀資料的那個檔案群組

前提是：從該檔案群組被備份的時間點開始，一直到交易記錄結尾為止，都擁有完整的交易記錄鏈。

我知道有人會說可以備份個別檔案，但我覺得沒什麼意義
因為群組裡面的資料都是均勻分散寫入的，備份一個檔案沒什麼用。
直接以群組為單位最好

策略五、部分備份

備份所有可讀寫的 filegroup，對於 read-only 的 filegroup 不處理。

這對有大量封存資料的資料庫非常有幫助。

備份實做

1. 先建立要用的測試檔

CREATE DATABASE TEST
ON PRIMARY
( 
    NAME = 'TEST', 
    FILENAME = 'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\DATA\TEST.mdf'
),
FILEGROUP FileGroupA
( 
    NAME = 'TESTFileA', 
    FILENAME = 'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\DATA\TESTFileA.ndf' 
),
FILEGROUP FileGroupB
( 
    NAME = 'TESTFileB', 
    FILENAME = 'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\DATA\TESTFileB.ndf' 
)
LOG ON
( 
    NAME = 'TEST_log', 
    FILENAME = 'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\DATA\TEST_log.ldf' 
);
GO

ALTER DATABASE [TEST] SET RECOVERY FULL;
GO

USE TEST;
GO

CREATE TABLE dbo.Contacts
(
    ContactID   INT           NOT NULL IDENTITY PRIMARY KEY,
    FirstName   NVARCHAR(30),
    LastName    NVARCHAR(30),
    AddressID   INT
) ON FileGroupA;

CREATE TABLE dbo.Addresses
(
    AddressID      INT          NOT NULL IDENTITY PRIMARY KEY,
    AddressLine1   NVARCHAR(50),
    AddressLine2   NVARCHAR(50),
    AddressLine3   NVARCHAR(50),
    PostCode       NCHAR(8)
) ON FileGroupB;

2.1 接下來會分成 GUI 跟 T-SQL 去做先講 GUI

先去選備份

然後會是下面這張圖

這個畫面選項都很好理解，只有 “只複製備份” 這個要特別說明一下

這個會依據上面備份類型的選擇去執行一次備份，但是他不會去改 DIFF pages。

DIFF pages 就是用來記錄現在交易紀錄鏈已經備份到哪了。

例如 :
做交易紀錄備份的時候，SQL Server 需要有一個地方去紀錄，這裡以前的交易紀錄已經被備份過了，所以下一次要再做交易紀錄備份，就從這個紀錄的點開始往後做。

但是如果選只複製備份，那就會只去做交易紀錄備份，不改 DIFF pages，然後下一次還要做交易紀錄備份的時候，他會跑去看更前面的節點。

這也意味，選這個選項，交易紀錄不會被截斷。

例如 :
做完整備份，然後有選這個選項，那下次差異備份，也不會是從這次完整備份開始去做，他會去再更上一次完整備份開始。

如果真的理解這個選項的功能，那馬上就可以想到，差異備份是不能選這個選項的。

選檔案群組的話就會看到剛剛建立的那幾個群組，可以指定備份哪一個

驗證就看你平常 dbcc checkdb 頻率有沒有高，他可以檢查檔案有沒有損毀，但是會更耗效能

2.2 備份 T-SQL

完整+差異+交易紀錄備份

執行完整備份

BACKUP DATABASE TEST
   TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak'
   WITH RETAINDAYS = 90
      , FORMAT
      , INIT
      , MEDIANAME = 'TEST'
      , NAME = 'TEST-Full Database Backup'
      , COMPRESSION;
GO

執行差異備份，然後放到同一個 media set

BACKUP DATABASE TEST
   TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak'
   WITH DIFFERENTIAL
      , RETAINDAYS = 90
      , NOINIT
      , MEDIANAME = 'TEST'
      , NAME = 'TEST-Diff Database Backup'
      , COMPRESSION;
GO

執行交易紀錄備份，一樣放同一個 media set

BACKUP LOG TEST
    TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak'
    WITH RETAINDAYS = 90
       , NOINIT
       , MEDIANAME = 'TEST'
       , NAME = 'TEST-Log Backup'
       , COMPRESSION;
GO

備份的 T-SQL 有很多選項可以選，列出來會太多，所以要用的時候上網查一下就好了，現在這裡也用不到。

這邊是圖方便所以都用同一資料夾，在正式環境中，最好用不同資料夾去分才好管理。

檔案群組備份策略

執行 file group 備份，這邊注意我的命名規則

BACKUP DATABASE TEST FILEGROUP = 'FileGroupA'
    TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTFGA.bak'
    WITH RETAINDAYS = 90
       , FORMAT
       , INIT
       , MEDIANAME = 'TESTFG'
       , NAME = 'TEST-Full Database Backup-FilegroupA'
       , COMPRESSION;
GO

完整備份，但是分散寫入

BACKUP DATABASE TEST
    TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTStripe1.bak',
       DISK =
'F:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTStripe12bak'
    WITH RETAINDAYS = 90
       , FORMAT
       , INIT
       , MEDIANAME = 'TESTStripe'
       , NAME = 'TEST-Full Database Backup-Stripe'
       , COMPRESSION;
GO

鏡像備份

BACKUP DATABASE TEST
    TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTStripe1.bak',
       DISK =
'F:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTStripe12bak'
    MIRROR TO DISK =
'G:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTMirror1.bak',
       DISK =
'H:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTMirror2.bak'
    WITH RETAINDAYS = 90
       , FORMAT
       , INIT
       , MEDIANAME = 'TESTMirror'
       , NAME = 'TEST-Full Database Backup-Mirror'
       , COMPRESSION;
GO

還原實作

1.1 GUI 還原

去點時間表，可以選擇要還原的時間點

右下角這邊有驗證備份，最好檢查一下，會檢查蠻多東西的，起碼可以避免還原白跑

檔案的地方沒什麼就讓你選要復原到哪而已

這裡的選項比較多

覆蓋現有的資料庫 : 如果目標 SQL Server 上面有同名的資料庫，還原的時候允許覆蓋。
保留複寫設定 : 就是保留 Replication 這東西是 Always on 會用到的東西
限制對還原資料庫的存取 : 還原完成後，不要讓一般使用者馬上進來，只讓特定高權限人員可以連線。
復原狀態
1. RESTORE WITH RECOVERY : 還原完成後，把資料庫正式打開，讓它變成可用狀態。
2. RESTORE WITH NORECOVERY : 就跟上面那個相反，還原後不打開保持 restoring 狀態
3. RESTORE WITH STANDBY : 還原後，資料庫唯讀。
待命資料檔案 : 只有復原狀態選 STANDBY 的時候可以選，資料庫裡面可能有一些還沒完成的交易，需要暫時 undo，讓資料庫保持一致狀態。這些 undo 資訊會存在 standby file 裡。
結尾紀錄就是結尾交易紀錄解釋過這是什麼了

1.2 T-SQL 還原

跟備份一樣，有一堆選項可以選，要用的時候再去查

USE master
GO

-- 備份交易記錄尾端
BACKUP LOG TEST
TO DISK =
N'H:\MSSQL\Backup\TEST_LogBackup_2026-06-18_13-38-49.bak'
    WITH NOFORMAT,
         NAME = N'TEST_LogBackup_2026-06-18_13-38-49',
         NORECOVERY,
         STATS = 5;

-- 還原完整備份
RESTORE DATABASE TEST
FROM DISK = N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak'
    WITH FILE = 1,
         NORECOVERY,
         STATS = 5;

-- 還原差異備份
RESTORE DATABASE TEST
FROM DISK = N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak'
    WITH FILE = 2,
         NORECOVERY,
         STATS = 5;

-- 還原交易記錄
RESTORE LOG TEST
FROM DISK = N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST.bak'
    WITH FILE = 3,
         STATS = 5;

GO

還原到時間點

剛剛都是點對點還原

接下來要做還原到時間點

但是要做之前，要先處理一些資料做出差異才看的出來

USE TEST
GO

--完整備份一次
BACKUP DATABASE TEST
    TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTPointinTime.bak'
    WITH RETAINDAYS = 90
       , FORMAT
       , INIT, SKIP
       , MEDIANAME = 'TESTPoint-in-time'
       , NAME = 'TEST-Full Database Backup'
       , COMPRESSION;

-- 插入資料1
INSERT INTO dbo.Addresses
VALUES ('1 Carter Drive', 'Hedge End',
'Southampton', 'SO32 6GH')
     , ('10 Apress Way', NULL, 'London', 'WC10 2FG');

-- 交易紀錄備份1
BACKUP LOG TEST
    TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTPointinTime.bak'
    WITH RETAINDAYS = 90
       , NOINIT
       , MEDIANAME = 'TESTPoint-in-time'
       , NAME = 'TEST-Log Backup'
       , COMPRESSION;

--插入資料2
INSERT INTO dbo.Addresses
VALUES ('12 SQL Street', 'Botley', 'Southampton',
'SO32 8RT')
     , ('19 Springer Way', NULL, 'London',
'EC1 5GG');

--清空表
TRUNCATE TABLE dbo.Addresses;

--交易紀錄備份2
BACKUP LOG TEST
    TO DISK =
'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTPointinTime.bak'
    WITH RETAINDAYS = 90
       , NOINIT
       , MEDIANAME = 'TESTPoint-in-time'
       , NAME = 'TEST-Log Backup'
       , COMPRESSION;
GO

這個一連串動作是為了模擬 Address 被不小心 truncate了，然後我們要還原到 truncate 之前。

如果要做到這件事

要知道 truncate 發生的精確時間，然後還原日期時間。 < 這通常不可能
更準確來說，需要找出truncate 發生所在的交易 LSN 然後還原到這個交易之前。

我們可以使用一個名為 sys.fn_dump_dblog() 的系統函數，來顯示最後一個交易記錄備份的內容。

然後現在因為再模擬，所以可以用肉眼就看出，交易紀錄備份2 裡面，會有包含一個 INSERT紀錄跟一個 TRUNCATE 紀錄。

這個系統函數，他一定要輸入 68 個參數，對一定要，少一個都不行。

第一根第二個參數是指定開始與結束的 LSN，用來篩選結果，設定為 NULL 可以回傳備份中所有項目
第三個參數是指定這個備份檔當是 disk 還是 tape
第四個參數指定這個備份在 backup device 中的順序 ID。
接下來 64 個參數接受media set 中 backup devices 的名稱，如果沒有那麼多，空的都寫 DEFAULT

SELECT
    CAST(
        CAST(
            CONVERT(VARBINARY, '0x'
                + RIGHT(REPLICATE('0', 8)
                + SUBSTRING([Current LSN], 1, 8), 8), 1
            ) AS INT
        ) AS VARCHAR(11)
    )
    + RIGHT(REPLICATE('0', 10) +
        CAST(
            CAST(
                CONVERT(VARBINARY, '0x'
                    + RIGHT(REPLICATE('0', 8)
                    + SUBSTRING([Current LSN], 10, 8), 8), 1
                ) AS INT
            ) AS VARCHAR(10)
        ), 10)
    + RIGHT(REPLICATE('0', 5) +
        CAST(
            CAST(CONVERT(VARBINARY, '0x'
                + RIGHT(REPLICATE('0', 8)
                + SUBSTRING([Current LSN], 19, 4), 8), 1
            ) AS INT
        ) AS VARCHAR
    ), 5) AS ConvertedLSN
    , *
FROM
    sys.fn_dump_dblog (
        NULL, NULL, N'DISK', 3,
				N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTPointinTime.bak',
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT,
        DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE [Transaction Name] = 'TRUNCATE TABLE';

前面 cast 一堆東西是在準備還原的 LSN，因為系統函數給的 LSN，不是還原要求的指定格式，所以我只是把他調成指定格式而已。

上圖可以看出 LSN 是 : 44000000256000001

接下來拿到這個 TRUNCATE 的 LSN 就可以開始還原到 TRUNCATE 之前了

USE master
GO

RESTORE DATABASE TEST
    FROM DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTPointinTime.bak'
    WITH FILE = 1
       , NORECOVERY
       , STATS = 5
       , REPLACE;

RESTORE LOG TEST
    FROM DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTPointinTime.bak'
    WITH FILE = 2
       , NORECOVERY
       , STATS = 5
       , REPLACE;

RESTORE LOG TEST
    FROM DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTPointinTime.bak'
    WITH FILE = 3
       , STATS = 5
       , STOPBEFOREMARK =
'lsn:44000000256000001'
       , RECOVERY
       , REPLACE;

但這是理想狀態。因為正式環境中不可能當下就知道這個 TRUNCATE 是在哪一個備份檔案裡。

如果仔細看這個還原，會發現，這裡有一個3。

而這個 3 代表在整個 backup device 中，他是第三個 backup set

但這是因為在模擬，所以我用眼睛看那個我故意做的 truncate，當然馬上就可以知道，我應該要去還原第三個 backup set 的 lsn。

如果是正式環境，不知道是哪一個，但是大約知道 truncate 的時間點，可以用這個去找最近一次的備份的 position 是多少然後再帶入。

SELECT TOP (20)
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type,
    bs.first_lsn,
    bs.last_lsn,
    bs.position,
    bmf.physical_device_name,
    bs.name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = N'TEST' --資料庫名稱
  AND bs.type = 'L'
  AND bs.backup_finish_date >= DATEADD(HOUR, -2, '2026-06-18T14:37:00')
  AND bs.backup_start_date  <= DATEADD(HOUR,  2, '2026-06-18T14:37:00')
ORDER BY bs.backup_start_date;

還原檔案

可能會遇到這種情況：資料庫中只有某些檔案或檔案群組損毀。

如果是這種情況，只要我們擁有完整的交易記錄鏈，就可以只還原損毀的檔案。

但是這條完整的交易記錄鏈必須涵蓋：
從執行檔案或檔案群組備份的時間點到 transaction log 的結尾

接下一樣，在開始備份之前要先做一些資料異動，才看得出結果。

--插入資料
INSERT INTO dbo.Contacts
VALUES ('Peter', 'Carter', 1),
       ('Danielle', 'Carter', 1);
       
-- 備份GroupA 因為Contacts當初建立在 GroupA 所以也是等於備份Contacts的意思
BACKUP DATABASE TEST FILEGROUP =
N'PRIMARY', FILEGROUP = N'FileGroupA'
    TO DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTFileRestore.bak'
    WITH FORMAT
       , NAME = N'TEST-Filegroup Backup'
       , STATS = 10;
       
-- 插入 Addressses
INSERT INTO dbo.Addresses
VALUES ('SQL House', 'Server Buildings', NULL,
'SQ42 4BY'),
       ('Carter Mansions', 'Admin Road',
'London', 'E3 3GJ');

-- 備份交易紀錄
BACKUP LOG TEST
    TO DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTFileRestore.bak'
    WITH NOFORMAT
       , NOINIT
       , NAME = N'TEST-Log Backup'
       , NOSKIP
       , STATS = 10;

所以現在我有

FileGroupA 的完整備份
最後一次交易紀錄備份

然後假設現在 GroupA 壞掉了，我要還原 GroupA

流程會是

先做結尾交易紀錄備份 ( 任何時候災難發生，都應該立刻去嘗試做結尾交易紀錄備份 )
然後還原 FILE A
把一開始備份的交易紀錄還原
還原剛剛做的結尾交易紀錄備份

USE master
GO
BACKUP LOG TEST
    TO DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST_LogBackup_2026-06-18_12-26-09.bak'
    WITH NOFORMAT
       , NOINIT
       , NAME = N'TEST_LogBackup_2026-06-18_12-26-09'
       , NOSKIP
       , NORECOVERY
       , STATS = 5;
       
       
RESTORE DATABASE TEST FILE =
N'TESTFileA'
    FROM DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTFileRestore.bak'
    WITH FILE = 1
       , NORECOVERY
       , STATS = 10
       , REPLACE;
GO

RESTORE LOG TEST
    FROM DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TESTFileRestore.bak'
    WITH FILE = 2
       , STATS = 10
       , NORECOVERY;

RESTORE LOG TEST
    FROM DISK =
N'E:\Program Files\Mircosoft  SQL Server\MSSQL17.WU\MSSQL\Backup\TEST_LogBackup_2026-06-18_12-26-09.bak'
    WITH FILE = 1
       , STATS = 10
       , RECOVERY;
GO

這邊有一個事情跟一般的備份完全不同，而且很重要，就是結尾交易紀錄備份。

一般來說如果去還原資料庫，沒有做結尾交易紀錄備份，那頂多就失去那幾分鐘的紀錄，整個資料庫還是可以運行。

但是如果是還原單一檔案，然後你又沒有去做結尾交易紀錄備份的話，那整個資料庫可能就會無法 RECOVERY 成功。

原因是，會讓 fileA 跟 fileB 沒有一致的 LSN。

假設一個情境，依照我們剛剛寫的語法

10 : 00 插入 Contacts，也就是在 File A 寫資料

10 : 10 備份 File A

10 : 20 插入 Addresses，也就是在 File B 寫資料

10 : 30 做交易紀錄備份

10 : 40 繼續插入 Addresses

10 : 50 File A損壞

然後我們現在只去
還原 File A，然後再接上 10 : 30 做的交易紀錄備份

那這個時候 File A 的狀態應該是 10 : 30
但與此同時 File B 的狀態已經是 10 : 50 了

會造成時間不一致，也就是 LSN 不一致，SQL Server 就會還原失敗。

所以這時候只有兩種辦法

一開始就做結尾交易紀錄備份，這樣 FileA 還原到 10 : 30 的時候，可以繼續還原到 10 : 50，即使這段時間 FileA 並沒有做任何更動，還是要還原。
妳可以接受 FileB 跟其他所有相關的檔案也還原回去 10 : 30，這樣不用結尾交易紀錄備份也可以。

還原 PAGE

那如果只有某個資料頁損毀呢? 一樣可以只還原某個資料頁。

好處是，可以大幅減少停機時間。

壞處就只是這很複雜。

等等會用到 DBCC WRITEPAGE ，跟之前一樣，這只是為了示範故意損壞 PAGE，正式環境絕對不要使用。

一樣先備份整個資料庫

-- Back up the database

BACKUP DATABASE TEST
    TO DISK =
N'H:\MSSQL\Backup\TESTPageRestore.bak'
    WITH FORMAT
       , NAME = N'TEST-Full Backup'
       , STATS = 10;

接下來我故意去損壞 PAGE

-- Corrupt a page in the Contacts table

ALTER DATABASE TEST SET SINGLE_USER WITH NO_WAIT;
GO

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = 'DBCC WRITEPAGE(' +
(
    SELECT CAST(DB_ID('TEST') AS NVARCHAR)
) +
', ' +
(
    SELECT TOP 1 CAST(file_id AS NVARCHAR)
    FROM dbo.Contacts
    CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
) +
', ' +
(
    SELECT TOP 1 CAST(page_id AS NVARCHAR)
    FROM dbo.Contacts
    CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
) +
', 2000, 1, 0x61, 1)';

EXEC(@SQL);

ALTER DATABASE TEST SET MULTI_USER;
GO

然後現在我們再去看 Contacts 這張表，就會發現有不一致的錯誤。

這邊值得注意的是可以看到 : ….發生在頁面 ( 3 : 8) 的..…

這個 3 : 8 等等會用到

也可以用之前提到過的 MSDB..suspect_pages 來看

還原頁面

USE Master
GO
-- 結尾交易紀錄備份
BACKUP LOG TEST
    TO DISK =
N'H:\MSSQL\Backup\TEST_LogBackup_2022-02-17_16-47-46.bak'
    WITH NOFORMAT, NOINIT
       , NAME = N'TEST_LogBackup_2022-02-17_16-32-46'
       , NOSKIP
       , STATS = 5;
-- 還原 3:8 頁面
RESTORE DATABASE TEST PAGE='3:8'
    FROM DISK =
N'H:\MSSQL\Backup\TESTPageRestore.bak'
    WITH FILE = 1
       , NORECOVERY
       , STATS = 5;

-- 結尾交易紀錄備份
RESTORE LOG TEST
    FROM DISK =
N'H:\MSSQL\Backup\TEST_LogBackup_2022-02-17_16-47-46.bak'
    WITH STATS = 5
       , RECOVERY;
GO

就還原成功了

分段還原

這是一種把資料庫中的 filegroups 一個一個帶上線的功能

好處是如果今天是整的大型資料庫損毀，你可以一個一個讓他 online 不用等全部還原好才 online。

但是要達成這個目的之前，必須有全部的 filegroup backup 跟交易紀錄備份

BACKUP DATABASE TEST
    FILEGROUP = N'PRIMARY', FILEGROUP =
N'FileGroupA', FILEGROUP = N'FileGroupB'
    TO DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH FORMAT
       , NAME = N'TEST-Filegroup Backup'
       , STATS = 10;

BACKUP LOG TEST
    TO DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH NOFORMAT, NOINIT
       , NAME = N'TEST-Full Database Backup'
       , STATS = 10;

接下來還原 Primary

USE master
GO

-- 備份結尾交易紀錄
BACKUP LOG TEST
    TO DISK =
N'H:\MSSQL\Backup\TEST_LogBackup_2026-06-19_27-29-46.bak'
    WITH NOFORMAT, NOINIT
       , NAME = N'TEST_LogBackup_2026-06-19_17-29-46'
       , NOSKIP
       , NORECOVERY
       , NO_TRUNCATE
       , STATS = 5;

RESTORE DATABASE TEST
    FILEGROUP = N'PRIMARY'
    FROM DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH FILE = 1
       , NORECOVERY
       , PARTIAL -- 這是部分還原檔案的關鍵字
       , STATS = 10;

RESTORE LOG TEST
    FROM DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH FILE = 2
       , NORECOVERY
       , STATS = 10;

RESTORE LOG TEST
    FROM DISK =
N'H:\MSSQL\Backup\TEST_LogBackup_2026-06-19_27-29-46.bak'
    WITH FILE = 1
       , STATS = 10
       , RECOVERY;

同樣的步驟還原剩下的 filegroup a、b

RESTORE DATABASE TEST
    FILEGROUP = N'FileGroupA'
    FROM DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH FILE = 1
       , NORECOVERY
       , STATS = 10;

RESTORE LOG TEST
    FROM DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH FILE = 2
       , NORECOVERY
       , STATS = 10;

RESTORE LOG TEST
    FROM DISK =
N'H:\MSSQL\Backup\TEST_LogBackup_2026-06-19_27-29-46.bak'
    WITH FILE = 1
       , STATS = 10
       , RECOVERY;

RESTORE DATABASE TEST
    FILEGROUP = N'FileGroupB'
    FROM DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH FILE = 1
       , NORECOVERY
       , STATS = 10;

RESTORE LOG TEST
    FROM DISK =
N'H:\MSSQL\Backup\TESTPiecemeal.bak'
    WITH FILE = 2
       , NORECOVERY
       , STATS = 10;

RESTORE LOG TEST
    FROM DISK =
N'H:\MSSQL\Backup\TEST_LogBackup_2026-06-19_27-29-46.bak'
    WITH FILE = 1
       , STATS = 10
       , RECOVERY;

加速還原

當一個 instance 在非預期事件之後啟動時，如果你沒有辦法先將 instance 放入安全狀態，那麼根據 workload 的不同，資料庫可能需要很長時間才能重新上線。

這種情況特別容易發生在有長時間執行交易的時候。

這是因為 SQL Server 用來復原資料庫的機制所造成的。

復原會分成三個階段

分析階段

SQL Server 突然掛掉重開後，他第一件事情不是馬上修復資料，他是會先去看交易紀錄，判斷掛掉的那一刻，那些交易已經成功? 那些又只做到一半?

那他會去看最後一個 checkpoint 開始掃描交易紀錄。因為 checkpoint 代表 SQL Server 曾經在那個時間點附近，把很多 dirty pages 寫回資料檔，並在log 裡標記一個 recovery 可以開始參考的位置。

那所以也會有一種可能就是，已經修改過很久的 page，但是並沒有 checkpoint，所以她沒有寫到硬碟裡，那復原就要從更早的交易開始看。

看這些目的主要是還要判斷 commit。

如果已經 commit 那要 redo
如果還沒就要 undo

10:00 Transaction A 開始
10:01 A 修改 Page 100
10:02 checkpoint
10:03 Transaction B 開始
10:04 B 修改 Page 200
10:05 SQL Server crash

照這個時間軸，順利的話，SQL Server 開始分析，10:02那個 checkpoint，然後就知道要從這裡開始往後還原。

但問題是，並不是每次 checkpoint 都一定會把 dirty page 寫入 disk，例如這次 checkpoint 要很久還沒來的及把 page 100 寫入 disk，所以這時候就得去看更早之前的交易。

所以分析階段就是 SQL Server 要去判斷，他應該要從哪裡開始還原。

注意這裡說的還原，不是備份還原的還原，是你 instance 掛了，然後你重啟的時候，因為可能掛掉的時候，正在執行一些交易，SQL Server 要去看這些交易到底成功還是沒成功。

重啟階段

在這個階段，SQL Server 會從最早的未提交交易開始掃描 log，並且重新執行所有已提交交易的操作，一直到 SQL Server 停止的那個時間點為止。

REDO PHASE

這個階段會從 transaction log 的結尾開始，往回執行到最後一個未提交交易，並且 rollback 所有在 instance 停止之前尚未提交的交易。

ADR改進

SQL Server 2019 引入了一個新的復原程序，稱為 Accelerated Database Recovery，ADR。

這個程序在 SQL Server 2022 中又被改進。

ADR 是 SQL Server 2019 開始的新 recovery 機制，它用 PVS 把資料版本存在「使用者資料庫」裡，讓 rollback / crash recovery 變快。

這會有一個副作用：增加該資料庫的儲存需求，但會降低 TempDB 的儲存需求，因為 PVS 會取代原本位於 TempDB 中的 version store。

而他主要解決兩件事情

SQL Server crash 後，資料庫 recovery 太久
大交易 rollback 太久

PVS 是一個把資料的舊版本存在資料庫裡的一個機制。

所以當你 CRASH 的時候，SQL Server 會很快就知道哪一個版本是正確的，不用像傳統的 recovery 那樣，花很久一筆一筆 undo。

接著，一個稱為 logical revert 的非同步程序，可以對儲存在 PVS 中的交易執行即時 rollback。

這是因為這些版本會被標記為 aborted，然後可以直接被忽略
ADR 也會引入一個位於記憶體中的 secondary log stream。

這個 secondary log stream 用來記錄那些無法使用 PVS version store 的操作交易，例如 DDL 操作。

這個 secondary log stream 稱為 SLOG。

它會在 checkpoint 作業期間，將記錄序列化到磁碟，並且在交易 commit 時被截斷。

此外，也會實作一個非同步的 Cleaner process。

這個程序會定期清理已經不再需要的 page versions。

當資料庫啟用 ADR 時，復原仍然包含三個階段，但這些階段會針對新的機制進行最佳化。

在 analysis phase 中，SQL Server 會從最後一個 checkpoint 開始掃描 transaction log，或者從最早修改了某個 page、而該 page 仍然是 dirty 的交易開始掃描。

它會判斷在 SQL Server service 停止的那個時間點，每一個交易的狀態。

它也會從 SLOG 收集 nonversioned operations。

redo phase 會被分成兩個部分。

第一部分中，交易會從 SLOG 重新提交。

第二部分中，來自 transaction log 的操作會被 redo。

不過，ADR 的設計表示這個 redo phase 可以從最後一個 checkpoint 開始，或者從最後一個 dirty page 的交易開始，而不是從最後一個未提交交易開始。

undo phase 非常快。

這是因為 logical revert process 可以快速判斷 PVS 中哪些版本已經 aborted，並且可以從 SLOG undo 任何 nonversioned transactions。

ADR 也會為參與某些使用情境的資料庫帶來好處，例如 ETL loads。

在這類情境中，可能會發生大型操作或 bulk inserts，造成長時間執行的交易。

傳統上，這可能會導致 transaction log 變得非常大。

ADR 允許在 log backup 和 checkpoints 時進行更積極的 log truncation，因為 log 不再需要從最後一個未提交交易開始處理。

-- 啟用 ADR
ALTER DATABASE TEST SET
ACCELERATED_DATABASE_RECOVERY = ON

在這個範例中，我們沒有指定 PVS 應該儲存在哪個 filegroup 上。

因此，它會儲存在預設 filegroup 上，而預設是 PRIMARY。

不過，我建議將 PVS 儲存在一個獨立的 filegroup 上，並且將這個 filegroup 放在盡可能最快的儲存設備上。

一旦 ADR 啟用之後，如果你要變更 PVS filegroup，就必須先停用 ADR。

--Disable ADR

ALTER DATABASE TEST SET
ACCELERATED_DATABASE_RECOVERY = OFF
GO

--Cleanup the existing PVS

EXEC sys.sp_persistent_version_cleanup TEST
GO

--Add PVS Filegroup

ALTER DATABASE TEST ADD FILEGROUP PVS_FG
GO

--Add file to PVS Filegroup

ALTER DATABASE TEST ADD FILE (
    NAME = 'PVS'
    , FILENAME = N'C:\MSSQL\DATA\PVS.ndf'
)
TO FILEGROUP PVS_FG
GO

--Enable ADR using new Filegroup for PVS

ALTER DATABASE TEST SET
ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = PVS_FG);
GO

SQL Server 2022 的一項改進，是可以指定 cleanup process 每個 database 可使用的 thread 數量。

在 SQL Server 2019 中，整個 instance 只有一個 thread。

thread 數量是可以設定的；當 instance 上有多個大型資料庫時，增加 thread

【基礎】 13.稽核 & Ledger

【基礎】 15.HA & DR 概念

系列文

SQL Server 基礎&調教共 17 篇

RSS系列文訂閱系列文

3 人訂閱

完整目錄

熱門推薦

{{ item.channelVendor }} | {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

232 組

團體組數

7 組

累計文章數

1826 篇

最後報名日

9/15

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 17th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# 18th鐵人賽 linux windows server css

IT邦幫忙

SQL Server 基礎&調教系列 第 14 篇