15.5.1 Enterprise Manager 圖形化還原流程
- EM → 選取伺服器 → 主選單 Tools → Restore Database
- 彈出 Restore Database 對話框
General 索引標籤關鍵設定
- Restore as database：目標資料庫名稱 (可異地還原至新資料庫)
- Restore 類型：Database / Transaction Log / File/Filegroup
- Show backup of database：選擇來源資料庫，下方顯示備份歷史
- First backup to restore：選擇起始備份集 (預設最近)
Options 索引標籤進階選項
| 選項 | 說明 |
|---|
| Eject Tapes After Restoring Each Backup | 每個備份集還原後彈出磁帶 |
| Prompt Before Restoring Each Backup | 每個備份集還原前提示確認 (適合手動多備份集還原) |
| Force Restore over Existing Database | 強制覆蓋現有資料庫檔案 (異地還原常用) |
| Restore Database Files As | 網格中 Restore As 欄位可變更檔案還原路徑 (異地還原必備) |
| Recovery Completion State (三選一，決定還原後資料庫狀態) |
| → Leave Database Operational | 完成所有還原 (含記錄)，資料庫立即可用 (最終步驟選此) |
| → Leave Database Non-operational But Able to Restore Additional Transaction Logs | 僅還原資料庫/差異，保持恢復中狀態，待後續記錄備份 (中間步驟選此) |
| → Leave Database Read-only And Able to Restore Additional Transaction Logs | 唯讀待命模式 (Log Shipping 次要伺服器常用) |
15.5.2 RESTORE T-SQL 指令完整語法與範例
核心還原指令範例
完整備份還原 (異地、變更路徑)
RESTORE DATABASE MyDB_Test
FROM DISK = 'D:\Backup\MyDB_Full_20240115.bak'
WITH
MOVE 'MyDB_Data' TO 'D:\Data\MyDB_Test.mdf',
MOVE 'MyDB_Log' TO 'D:\Log\MyDB_Test.ldf',
REPLACE, -- 覆蓋同名資料庫
STATS = 10,
NORECOVERY; -- 待後續記錄備份
差異備份還原
RESTORE DATABASE MyDB_Test
FROM DISK = 'D:\Backup\MyDB_Diff_20240115_1800.bak'
WITH NORECOVERY, STATS = 10;
記錄備份依序還原
RESTORE LOG MyDB_Test
FROM DISK = 'D:\Backup\MyDB_Log_20240115_1830.trn'
WITH NORECOVERY, STATS = 10;
-- 最後一筆記錄備份 (或指定時間點)
RESTORE LOG MyDB_Test
FROM DISK = 'D:\Backup\MyDB_Log_20240115_1900.trn'
WITH RECOVERY, STATS = 10; -- 完成還原，資料庫上線
-- 或時間點還原：
-- WITH RECOVERY, STOPAT = '2024-01-15 18:45:00';
驗證備份集資訊 (不還原)
RESTORE HEADERONLY FROM DISK = 'D:\Backup\MyDB_Full_20240115.bak';
RESTORE FILELISTONLY FROM DISK = 'D:\Backup\MyDB_Full_20240115.bak';
還原順序鐵律
- 完整備份 (WITH NORECOVERY)
- 最近一次差異備份 (WITH NORECOVERY) — 若有
- 所有記錄備份依序 (WITH NORECOVERY)
- 尾部記錄備份** (若有、且 LDF 可存取) (WITH NORECOVERY)
- 最後一筆記錄 / STOPAT (WITH RECOVERY) — 資料庫上線
嚴禁跳過順序、嚴禁遺漏記錄備份**，否則資料庫將處於不一致狀態，無法上線。
