五餅二魚工作室

1. 備份觀念總覽：復原模式與三種備份的關係

在動手做備份之前，必須先搞懂三件事的關係：復原模式（Recovery Model）、備份類型、以及 LSN（Log Sequence Number，記錄序號）。這三者是後面所有操作的基礎，觀念不清楚，練習做再多次也只是背指令。

---

1.1 復原模式決定了「交易記錄」的行為

SQL Server 有三種復原模式：

- Simple（簡單模式）：交易記錄在 Checkpoint 之後會被自動清除，因此無法做交易記錄備份，也無法還原到某個時間點。適合開發、測試或可以容忍遺失資料的資料庫。
- Full（完整模式）：交易記錄會完整保留，直到被交易記錄備份清除為止。這是正式環境最常用的模式，因為可以做到「時間點還原（Point-in-Time Recovery）」，資料遺失風險最小。
- Bulk-logged（大量記錄模式）：介於兩者之間，針對大量匯入等操作會用最小記錄方式處理，還原精確度會比 Full 模式差一些，通常只在大量 ETL 時暫時切換使用。

-- 設定 Recovery Mode 為簡單模式（交易記錄無法備份，也無法做時間點還原）
ALTER DATABASE [Source] SET RECOVERY SIMPLE
GO

-- 設定 Recovery Mode 為完整模式（可做交易記錄備份，支援時間點還原）
ALTER DATABASE [Source] SET RECOVERY FULL
GO

重要觀念：如果資料庫是 Full 模式，但從來沒有做過交易記錄備份，交易記錄檔（LDF）只會一直長大，不會被清空。很多「交易記錄檔爆炸」的案例，都是因為只做完整備份、卻忘了做排程交易記錄備份，導致交易紀錄檔無法循環使用只好一值長大。

---

1.2 三種備份類型的關係

備份類型	指令	用途	是否需要先有完整備份
完整備份	BACKUP DATABASE	備份資料庫在某個時間點的完整內容，是所有還原的基準點	不用
差異備份	BACKUP DATABASE ... WITH DIFFERENTIAL	備份自「上一次完整備份」以來所有變動過的資料頁（Extent）	需要
交易記錄備份	BACKUP LOG	備份自「上一次記錄備份」以來的所有交易記錄，可用來做時間點還原	需要

---

2. 練習環境建置

以下練習沿用之前範例的架構：在磁碟機 E: 建立三個目錄，DB 放資料庫檔案、Script 放練習用指令檔、Backup 放備份檔案。正式環境請依實際磁碟規劃調整路徑，並注意備份目的地建議與資料庫檔案分開存放的磁碟或磁區，避免同一顆硬碟損毀時備份與資料庫同時遺失。

-- 建立範例資料庫
CREATE DATABASE [Source]
 ON  PRIMARY
( NAME = 'Source'    , FILENAME = 'E:\DB\Source.MDF' , SIZE = 40MB , FILEGROWTH = 10MB )
 LOG ON
( NAME = 'Source_log', FILENAME = 'E:\DB\Source.LDF' , SIZE = 10MB , FILEGROWTH = 10MB )
GO

-- 設定 Recovery Mode 為完整模式，才能練習交易記錄備份
ALTER DATABASE [Source] SET RECOVERY FULL
GO

放入一些測試資料，方便後續比對備份還原後的資料筆數是否正確：

USE [Source]
GO
-- 關閉「(1000 rows affected)」這類訊息，讓輸出乾淨一點
SET NOCOUNT ON
-- 建立範例資料表
CREATE TABLE [TestTable] ( [Field1] INT )
GO
-- 建立 1000 筆測試資料 (0~999)
DECLARE @PTR INT = 0
WHILE @PTR < 1000
BEGIN
 INSERT INTO [TestTable] VALUES ( @PTR )
 SET @PTR += 1
END

---

3. 完整備份（Full Backup）與備份檔檢查指令

資料表有資料之後，第一步一定是做完整備份，因為差異備份與交易記錄備份都必須依附在某一次完整備份之上才有意義。

USE [master]
GO
-- 完整備份資料庫，WITH INIT 表示覆蓋掉備份檔內原有內容（若檔案已存在）
BACKUP DATABASE [Source] TO DISK = 'E:\Backup\Source_Full.BAK' WITH INIT
GO

備份完成後，強烈建議養成「備份完就檢查」的習慣。以下三個指令都不會真的執行還原動作，只是用來檢查備份檔案本身，適合放進備份排程的後續步驟，自動驗證備份是否可用：

-- 驗證備份檔案的完整性（頁面總和檢查碼是否正確），確保備份檔沒有毀損
RESTORE VERIFYONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
GO
-- 列出這個備份檔內包含哪些實體資料檔/記錄檔（檔名、邏輯名稱、實體路徑）
-- 還原到新主機時常用這個指令先看清楚原始檔案配置，才能決定 MOVE 要怎麼寫
RESTORE FILELISTONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
GO
-- 列出備份檔的中繼資料：備份類型、備份時間、資料庫復原模式、LSN 範圍等
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
GO

---

4. 交易記錄備份（Log Backup）與 LSN

接下來練習交易記錄備份。為了讓大家看出差異，每次備份前都會先刪除一些資料，並把所有交易記錄備份都寫到同一個檔案裡（用來模擬同一個備份裝置累積多個備份組的情境）。
注意：只有第一次交易記錄備份要加 `WITH INIT`（建立新檔案），之後的備份都不能再加 INIT，否則會把前面的備份組洗掉。

4.1 第一次刪除 ( 應該資料剩下 990 筆 )

USE [Source]
GO
SET NOCOUNT ON
GO
-- 亂數刪除 10 筆資料 (0~200 之間)
DECLARE @PTR INT = 0
WHILE @PTR < 10
BEGIN
 DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 )
 SET @PTR += 1
END
SELECT COUNT(*) FROM [TestTable]
GO

備份交易紀錄檔

-- 第一個交易記錄備份檔案（建立新檔案）
BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK' WITH INIT
GO

4.2 第二次刪除 ( 應該資料剩下 980 筆 )

-- 亂數刪除 10 筆資料 (200~400 之間)
DECLARE @PTR INT = 0
WHILE @PTR < 10
BEGIN
 DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 200
 SET @PTR += 1
END
SELECT COUNT(*) FROM [TestTable]
GO

備份交易紀錄檔

-- 第二個交易記錄備份，不能加 INIT，會附加在同一個檔案中
BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK' 

4.3 第三次刪除 ( 應該資料剩下 970 筆 )

-- 亂數刪除 10 筆資料 (400~600 之間)
DECLARE @PTR INT = 0
WHILE @PTR < 10
BEGIN
 DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 400
 SET @PTR += 1
END
SELECT COUNT(*) FROM [TestTable]
GO

備份交易紀錄檔

-- 第二個交易記錄備份，不能加 INIT，會附加在同一個檔案中
BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK' 

用 RESTORE HEADERONLY 同時查看完整備份與交易記錄備份檔的資訊：

RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Log.BAK'

輸出結果裡面，對我們來說最重要的是 FirstLSN、LastLSN 和 DatabaseBackupLSN 這幾個欄位，正因為交易記錄備份是靠 LSN 首尾相接來串成一條「Log Chain」，只要中間有一個備份組遺失或損毀，鏈就斷了。斷點之後的所有交易記錄備份都無法用來還原，只能還原回斷點之前，或是重新做一次完整備份重建鏈。這也是為什麼交易記錄備份的排程頻率、保存與監控特別重要。

---

5. COPY_ONLY 備份

實務上常遇到這種情境：主管臨時要一份「現在」的完整備份帶去做別的用途（例如複製一份到測試環境），但你不希望這個「臨時的」完整備份打斷原本排程中差異備份的基準點（因為一般沒加 COPY_ONLY 的完整備份，會把差異備份的基準點重新設成這一次）。這時候就要用 WITH COPY_ONLY：

USE [Source]
GO
-- 完整備份加上 COPY_ONLY，不會影響差異備份的基準點，也不影響交易記錄鏈
BACKUP DATABASE [Source] TO DISK='E:\Backup\Source_Copy.BAK' WITH INIT, COPY_ONLY
GO

COPY_ONLY 同樣可以用在交易記錄備份上（BACKUP LOG ... WITH COPY_ONLY），效果是不會清空交易記錄，也不會影響原本排程的 Log Chain，適合臨時需要一份記錄備份、但不想打亂正常備份排程的情境。
 

---

6. 差異備份（Differential Backup）

繼續修改資料後 ( 資料應該剩下 960 筆 )，做一次差異備份：

USE [Source]
GO
-- 亂數刪除 10 筆資料 (600~800 之間)
DECLARE @PTR INT = 0
WHILE @PTR < 10
BEGIN
 DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 600
 SET @PTR += 1
END
SELECT COUNT(*) FROM [TestTable]
GO

BACKUP DATABASE [Source] TO DISK = 'E:\Backup\Source_Diff.BAK' WITH INIT, DIFFERENTIAL
GO

差異備份之後再繼續修改資料 ( 資料應該剩下 950 筆 )，產生第四個交易記錄備份：

USE [Source]
GO
-- 亂數刪除 10 筆資料 (800~1000 之間)
DECLARE @PTR INT = 0
WHILE @PTR < 10
BEGIN
 DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 800
 SET @PTR += 1
END
SELECT COUNT(*) FROM [TestTable]
GO

BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK'
GO

7. 備份時間軸與還原策略

把上面的操作依時間排出來，會是這樣的關係：

重點觀念：

- 差異備份永遠是「相對於最近一次完整備份」的增量，不是相對於上一次差異備份，因此同一輪備份週期內，差異備份檔案通常會越做越大。

- 交易記錄備份是彼此相連、不受完整備份或差異備份影響的獨立鏈條，除非做了新的完整備份（且不是 COPY_ONLY），才會開啟新一輪的差異備份基準。

- 差異備份的 LastLSN 一定落在某一個交易記錄備份的 FirstLSN 到 LastLSN 之間，這就是還原時可以用「完整備份 + 差異備份 + 差異備份之後的交易記錄備份」組合的原因。

檢查三種備份檔案的資訊：

RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_DIFF.BAK'
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Log.BAK'
GO

---

8. 還原練習：Log Chain 還原 vs. 差異備份還原

還原資料庫時，除了最後一個還原指令要加 `WITH RECOVERY`（讓資料庫恢復可用狀態），其餘中間步驟都要加 WITH NORECOVERY（讓資料庫保持「還原中」狀態，可以繼續接後面的還原指令）。

8.1 完整備份 + 全部交易記錄備份還原

-- 還原完整備份（加 NORECOVERY，因為後面還有交易記錄要接）
RESTORE DATABASE [Target] FROM DISK = 'E:\Backup\Source_Full.BAK' WITH NORECOVERY,
  MOVE 'Source'     TO 'E:\DB\Target.mdf',
  MOVE 'Source_log' TO 'E:\DB\Target.ldf'
GO

-- 檢查資料庫狀態，此時應顯示 RESTORING
SELECT name, state_desc FROM sys.databases WHERE name LIKE 'Target%'
GO

-- 依序還原四個交易記錄備份組
RESTORE LOG [Target] FROM DISK = 'E:\Backup\Source_Log.BAK' WITH FILE = 1, NORECOVERY
GO
RESTORE LOG [Target] FROM DISK = 'E:\Backup\Source_Log.BAK' WITH FILE = 2, NORECOVERY
GO
RESTORE LOG [Target] FROM DISK = 'E:\Backup\Source_Log.BAK' WITH FILE = 3, NORECOVERY
GO
RESTORE LOG [Target] FROM DISK = 'E:\Backup\Source_Log.BAK' WITH FILE = 4, RECOVERY
GO

還原後要是沒有問題 , 可以先用以下指令刪除資料庫

USE [master]
GO
ALTER DATABASE [Target] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO


DROP DATABASE [Target]
GO

8.2 完整備份 + 差異備份 + 差異之後的交易記錄備份

-- 還原完整備份（加 NORECOVERY）
RESTORE DATABASE [Target] FROM DISK = 'E:\Backup\Source_Full.BAK' WITH NORECOVERY,
  MOVE 'Source'     TO 'E:\DB\Target.mdf',
  MOVE 'Source_log' TO 'E:\DB\Target.ldf'
GO

-- 檢查資料庫狀態
SELECT name, state_desc FROM sys.databases WHERE name LIKE 'Target%'
GO

-- 還原差異備份，一次補上「完整備份之後、差異備份當下」的所有變動
RESTORE DATABASE [Target] FROM DISK = 'E:\Backup\Source_Diff.BAK' WITH NORECOVERY
GO

-- 只需要接上差異備份之後的交易記錄（也就是第四個），不需要重複還原前三個
RESTORE LOG [Target] FROM DISK = 'E:\Backup\Source_Log.BAK' WITH FILE = 4, RECOVERY
GO

常見迷思澄清：很多人以為「用了差異備份，就一定要配合差異備份還原，不能只用交易記錄備份還原」。這是錯的——只要交易記錄鏈完整，8.1 的方式永遠可行；差異備份的價值在於「縮短還原時間」（少接幾個交易記錄檔），而不是「唯一正確答案」。實務上該選哪一種，要看你保留了哪些備份檔案、以及還原時間要求（RTO）。

---

9. 資料庫加密（TDE）下的憑證備份與還原

如果資料庫啟用了 TDE（Transparent Data Encryption，透明資料加密），光備份資料庫檔案本身是不夠的。TDE 是靠伺服器層級的憑證（Certificate）來加密資料庫加密金鑰（DEK），這張憑證存放在 master 資料庫裡，不會包含在使用者資料庫的備份檔案中。如果只備份了資料庫、卻沒備份憑證，一旦原本主機的憑證遺失（例如整台主機報銷、或憑證被誤刪），這個資料庫備份就等於永久無法還原，資料直接報銷。這是 TDE 環境中最容易被忽略、後果也最嚴重的一個備份項目。

9.1 啟用 TDE 的基本步驟（背景說明）

USE [master]
GO

-- 1. 建立資料庫主金鑰（若尚未建立）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPasswod'
GO

-- 2. 建立用來加密 DEK 的憑證
CREATE CERTIFICATE TDECert_Source
WITH SUBJECT = 'TDE Certificate for Source Database'
GO

-- 3. 在要加密的資料庫中，建立資料庫加密金鑰，並指定用上面的憑證保護
USE [Source]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert_Source
GO

-- 4. 開啟 TDE
ALTER DATABASE [Source] SET ENCRYPTION ON
GO

9.2 備份憑證（最關鍵的一步）

憑證備份出來會產生兩個檔案：.cer（憑證公開部分）與 .pvk（私密金鑰，會用密碼加密保護）。這兩個檔案，加上下面設定的密碼，三者缺一不可，缺少任何一項都無法在別台主機還原這張憑證。

USE [master]
GO

BACKUP CERTIFICATE TDECert_Source
TO FILE = 'E:\Backup\TDECert_Source.cer'
WITH PRIVATE KEY (
    FILE = 'E:\Backup\TDECert_Source.pvk',
    ENCRYPTION BY PASSWORD = 'AnothorBackupPassword'
)
GO

- .cer、.pvk 這兩個檔案務必和資料庫備份檔（.bak）分開存放（例如不同的儲存位置或不同保管人），避免兩者一起遺失或一起被竊。常見做法是資料庫備份走一般備份儲存，憑證檔案另外走保密文件管理流程（例如加密雲端保險箱或實體保險箱）。

- `ENCRYPTION BY PASSWORD` 用的密碼，跟 `CREATE MASTER KEY` 的密碼是兩組不同的密碼，都要妥善保管（建議存放在密碼管理系統，並列入交接文件）。

- 每一次資料庫做完整備份後，都應該連帶確認對應的憑證備份是否存在且可用；如果憑證在資料庫加密之後又被重建（Regenerate）過，也要記得重新備份一次憑證。

- 建議定期（例如每季）在測試環境演練一次「用憑證備份 + 資料庫備份」完整還原，確認整組備份真的可用，而不是等到出事才發現密碼記錯或檔案遺失。

9.3 移轉到新主機：還原憑證

在新主機上，必須 先還原憑證，再還原（或附加）加密的資料庫，順序不能顛倒。

USE [master]
GO

-- 1. 新主機也要先有資料庫主金鑰（若尚未建立）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewServerMasterKeyPassword'
GO

-- 2. 用備份出來的 .cer / .pvk 還原憑證，密碼要和備份時 ENCRYPTION BY PASSWORD 用的一致
CREATE CERTIFICATE TDECert_Source
FROM FILE = 'E:\Backup\TDECert_Source.cer'
WITH PRIVATE KEY (
    FILE = 'E:\Backup\TDECert_Source.pvk',
    DECRYPTION BY PASSWORD = 'AnothorBackupPassword'
)
GO

-- 3. 憑證就緒後，才能正常還原加密的資料庫備份
RESTORE DATABASE [Source] FROM DISK = 'E:\Backup\Source_Full.BAK' WITH RECOVERY,
  MOVE 'Source'     TO 'E:\DB\Source.mdf',
  MOVE 'Source_log' TO 'E:\DB\Source.ldf'
GO

---

10. 主機移轉：把 SQL 帳號的 SID 與密碼轉成 Script

移轉資料庫到新主機時，另一個常被忽略、卻很常「踩雷」的環節是 SQL 帳號（SQL Login）。資料庫還原過去之後，資料庫內的使用者（Database User）會記住原本的 SID，但如果新主機上用一般方式重新建立同名的 Login，SQL Server 會給它一個全新的 SID，導致新舊 SID 對不起來——這就是俗稱的 孤兒使用者（Orphaned User）問題：登入帳號能連上 SQL Server，卻對應不到資料庫內的使用者權限。很多時候都會有朋友來詢問，明明我在新的主機上重建帳號的時候，都有使用相同的帳號和密碼，為什麼還不能用？這就是最主要的原因。以往再舊版本的時候，還要多寫一些把 Binary 轉成字串的方式，目前新版本的 SQL Server 都已經有新的函數可以使用，因此就順利整理一下新的語法

USE master;
GO

SELECT 
    'CREATE LOGIN [' + name + '] WITH PASSWORD = ' + 
    master.sys.fn_varbintohexstr(password_hash) + ' HASHED' +
    ', SID = ' + master.sys.fn_varbintohexstr(sid) +
    ', DEFAULT_DATABASE = [' + default_database_name + ']' +
    ', DEFAULT_LANGUAGE = [' + default_language_name + ']' +
    ', CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END +
    ', CHECK_POLICY = ' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + 
    ';' AS [Create_Login_Script]
FROM sys.sql_logins
WHERE is_disabled = 0
  AND name NOT LIKE '##%'               -- 排除系統內部帳號
  AND name NOT LIKE 'NT SERVICE%'
  AND name NOT LIKE 'NT AUTHORITY%'
  AND name <> 'sa';

sys.sql_logins 只列出 SQL 驗證帳號（不含 Windows 帳號），password_hash 欄位存放的就是加密後的密碼雜湊，SQL Server 可以直接拿雜湊值重建帳號，完全不需要知道明碼密碼，這也是這個做法在安全上可以放心使用的原因。

---

11. 總結

這篇文章把十年前那篇練習題重新梳理成一份完整的教學：從復原模式、三種備份的關係、LSN 與 Log Chain 的觀念，到還原策略的兩種組合方式，再補上兩個實務上真正會讓人「備份做了、關鍵時刻卻用不了」的坑——TDE 加密環境下遺漏憑證備份，以及主機移轉時 Login 的 SID 沒對齊造成孤兒使用者。備份從來不是「執行了 BACKUP 指令」就結束，而是要能保證在需要的那一刻，資料、憑證、帳號權限可以完整地一起復原回來。建議把憑證備份與 Login 遷移腳本都納入正式的災難復原（DR）文件與定期演練清單中，而不是等到真正需要移轉主機時才臨時現學。

如果你的環境裡也有加密資料庫，或是最近正準備搬遷主機，不妨現在就照著第 9、10的步驟，找一台測試機演練一次完整流程，確認憑證和帳號都能順利跟著資料庫一起復原過去，會比出事當下才臨時查資料安心得多。文章中若有任何指令在你的版本上跑起來不如預期，或是你有其他備份/遷移上的踩雷經驗，歡迎您也可以提出來讓我們一起學習一下囉。