SQL Server 批次還原腳本實戰：一鍵還原目錄下所有備份檔案的存儲過程完整版

業務場景與痛點

測試/開發環境部署常需還原百餘個資料庫。手動透過 SSMS 精靈或逐條執行 RESTORE DATABASE 極其低效。本文提供兩個企業級存儲過程：
① sp_RestoreDataBase — 單一備份檔智能還原
② sp_RestoreDataBase2 — 目錄批次還原編排器

核心技術基石：兩大系統函數

1. RESTORE HEADERONLY — 取得資料庫名與備份組資訊

RESTORE HEADERONLY FROM DISK = 'E:\DBBackup\dbA_2011-09-05.bak';

關鍵欄位：DatabaseName (原資料庫名)、BackupType (1=完整, 2=差異, 4=記錄)、Position (備份組序號)、CompressedBackupSize (2008+)。

2. RESTORE FILELISTONLY — 取得邏輯/物理檔案清單

RESTORE FILELISTONLY FROM DISK = 'E:\DBBackup\dbA_2011-09-05.bak';

關鍵欄位：LogicalName, PhysicalName, Type (D=資料, L=記錄, F=全文), FileID, TDEThumbprint (2008+ 加密指紋)。

還原命令動態構建邏輯

RESTORE DATABASE [TargetDB]
FROM DISK = 'E:\DBBackup\dbA_2011-09-05.bak'
WITH 
    FILE = <最後一個完整備份組的 Position>,
    MOVE 'LogicalData' TO 'E:\Data\TargetDB.mdf',
    MOVE 'LogicalLog'  TO 'E:\Data\TargetDB_Log.ldf',
    REPLACE, RECOVERY, STATS = 10;

難點：多資料檔/記錄檔/全文檔的邏輯名重命名、多備份組正確選擇 FILE 參數、NORECOVERY 串聯差異/記錄備份。

存儲過程 1：sp_RestoreDataBase (單檔智能還原 v3.3)

參數

核心功能亮點

• 路徑驗證：xp_fileexist 檢查備份檔存在；xp_cmdshell 'Dir ...' 驗證目標目錄可寫。
• 自動殺掉佔用連線：查詢 sys.sysprocesses 依 dbid 產生 KILL spid 批次。
• 智能檔案重命名：
  - 首個資料檔 → DBName.mdf
  - 其餘資料檔 → DBName_1.ndf, DBName_2.ndf...
  - 首個記錄檔 → DBName_Log.ldf
  - 其餘記錄檔 → DBName_Log_1.ldf...
  - 全文檔保持原邏輯名不改名。
• 多備份組自動處理：掃描 HeaderOnly 找出 最後一個完整備份組 (BackupType=1 且 Position 最大)，自動串聯後續差異/記錄備份 (NORECOVERY 直到最後一組 RECOVERY)。
• 版本相容：內建 SQL 2005 / 2008 兩套資料表變數結構 (含 CompressedBackupSize, TDEThumbprint)，自動適配。
• 錯誤捕獲：TRY...CATCH 回傳錯誤訊息與代碼 1，便於批次腳本判斷成敗。

EXEC dbo.sp_RestoreDataBase 
    'E:\DBBackup\dbA_2011-09-05.bak',
    'E:\DATA\SQL2008DE01';

-- 還原目錄下所有 .bak
EXEC dbo.sp_RestoreDataBase2 'E:\DBBackup';

-- 僅還原指定資料庫
EXEC dbo.sp_RestoreDataBase2 'E:\DBBackup', NULL, 'DB_A, DB_B, DB_C';