SQL Server 交易記錄備份與還原實戰：檔案組損壞恢復流程

示範資料庫新增 `.ndf` 次要檔案後遭刪除導致「疑似損壞」，透過**記錄備份（`NO_TRUNCATE`）+ 檔案組還原 + 記錄還原**完整恢復資料，零資料遺失。

模擬損壞場景

-- 1. 建立測試資料庫
CREATE DATABASE Test ON PRIMARY (NAME='Test_data', FILENAME='D:\Test_data.mdf')
LOG ON (NAME='Test_log', FILENAME='D:\Test_log.ldf');

-- 2. 建立測試表並寫入資料
CREATE TABLE Test.dbo.ta(id INT);
INSERT INTO Test.dbo.ta SELECT id FROM sysobjects;

-- 3. 完整備份
BACKUP DATABASE Test TO DISK='D:\Test.bak' WITH FORMAT;

-- 4. 新增 NDF 次要檔案
ALTER DATABASE Test ADD FILE (NAME='file', FILENAME='D:\test.ndf', SIZE=1MB);

-- 5. 模擬損壞：停止服務 → 刪除 test.ndf → 啟動服務 → 資料庫進入 SUSPECT/RECOVERY_PENDING

恢復步驟

1. 尾部記錄備份（關鍵）：
   `BACKUP LOG Test TO DISK='D:\Test_log.bak' WITH FORMAT, NO_TRUNCATE;`
   即使資料庫損壞，`NO_TRUNCATE` 仍能讀取作用中記錄檔內容。
2. 檔案組還原（`NORECOVERY`）：
   `RESTORE DATABASE Test FILEGROUP='PRIMARY' FROM DISK='D:\Test.bak' WITH NORECOVERY;`
   僅還原主要檔案組，保持資料庫處於恢復中狀態。
3. 記錄還原（`RECOVERY`）：
   `RESTORE LOG Test FROM DISK='D:\Test_log.bak' WITH RECOVERY;`
   套用尾部記錄，將資料庫帶至一致狀態並上線。

若已建立 NDF 檔案（檔案組不同）

記錄還原需改用 `WITH REPLACE` 覆寫現有檔案：

RESTORE LOG Test FROM DISK='D:\Test_log.bak' WITH REPLACE;

關鍵點總結

• **平時即建立完整備份 + 定期記錄備份**，才能在災難發生時擁有尾部記錄備份能力。
• `NO_TRUNCATE` 允許在資料庫離線/損壞時仍能備份作用中記錄，**不截斷記錄**，保證 LSN 連續。
• 還原序列：**完整備份 (NORECOVERY) → 差異備份 (NORECOVERY) → 記錄備份鏈 (NORECOVERY) → 尾部記錄 (RECOVERY)**。
• 檔案組還原可針對性恢復受損檔案，縮短 RTO。