SQL Server 資料庫復原模式設定雙軌教學：T-SQL 與 SSMS 圖形化

三種復原模式快速對比

模式	記錄備份	資料遺失風險	適用場景
FULL (完整)	必要	無 (可恢復至任意時間點)	生產核心庫、需時間點還原
BULK_LOGGED (大量記錄)	必要	大量操作期間可能遺失	大量匯入/索引重建窗口期
SIMPLE (簡單)	不支援	上次完整/差異備份後的所有變更	開發/測試/唯讀/可接受資料遺失

---

方法一：T-SQL 指令 (適合腳本化、自動化)

1. 設定為完整復原模式

ALTER DATABASE AdventureWorks SET RECOVERY FULL;

2. 驗證目前模式

SELECT name, recovery_model, recovery_model_desc
FROM sys.databases
WHERE name = 'AdventureWorks';

recovery_model：1=FULL, 2=BULK_LOGGED, 3=SIMPLE。

3. 切換為大量記錄 / 簡單模式

-- 大量記錄
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;

-- 簡單
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;

4. 修改預設復原模式 (影響後續新建資料庫)

ALTER DATABASE model SET RECOVERY SIMPLE; -- 或 FULL/BULK_LOGGED

> ⚠️ **警告**：若已建立記錄備份鏈，**嚴禁切換至 SIMPLE**，否則鏈條中斷，需重新完整備份才能恢復記錄備份。

---

方法二：SSMS 圖形化介面 (適合單次手動操作)

1. 物件總管 → 展開「資料庫」→ 右鍵目標資料庫 → 「屬性」。
2. 左側選「選項」頁籤。
3. 「復原模式」下拉選單選擇 完整 / 大量記錄 / 簡單。
4. 點「確定」套用。

---

最佳實踐與常見誤區

誤區	正確做法
資料庫建好就不管，預設 FULL 卻從未做記錄備份	FULL 模式**必須**搭配定期記錄備份，否則記錄檔無限增長導致磁碟爆滿
想省空間就改 SIMPLE，事後又想做時間點還原	SIMPLE 不支援記錄備份，無法時間點還原。需評估 RPO/RTO 後決定模式
大量匯入前改 BULK_LOGGED，匯入後忘記改回 FULL	大量操作結束後**立即改回 FULL** 並做一次完整備份，重建記錄鏈
model 資料庫模式未調整，新庫預設不符合規範	依組織標準設定 model 復原模式 (通常 FULL)，新庫自動繼承

---

快速檢查腳本

SELECT
    name AS DatabaseName,
    recovery_model_desc AS RecoveryModel,
    CASE
        WHEN recovery_model = 1 AND NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = d.name AND type = 'L' AND backup_finish_date > DATEADD(DAY, -1, GETDATE()))
            THEN '⚠️ FULL模式但超過24h無記錄備份'
        WHEN recovery_model = 3 THEN 'ℹ️ SIMPLE模式 (無記錄備份)'
        ELSE '✅ 正常'
    END AS HealthCheck
FROM sys.databases d
WHERE database_id > 4; -- 排除系統庫

總結

復原模式是備份策略的基石。**T-SQL 適合自動化部署與巡檢，SSMS 適合即時調整**。記住：FULL 必配記錄備份，SIMPLE 放棄時間點還原，切換模式謹慎操作並立即驗證備份鏈完整性。