從備份目錄自動產生 SQL Server 還原腳本：支援完整/差異/記錄備份鏈

DBA 常需在不同環境還原資料庫。本文提供一支 T-SQL 腳本，掃描備份檔目錄，依命名規則自動產生 RESTORE DATABASE ... WITH NORECOVERY 完整鏈腳本，涵蓋完整、差異、記錄備份。

前提假設

• 還原資料庫名稱、檔案路徑與備份來源相同。
• 備份檔命名格式：DBName_YYYYMMDDHHMM.XXX (XXX: BAK=完整, DIF=差異, TRN=記錄)。
• xp_cmdshell 已啟用。
• 無遺失記錄備份導致 LSN 斷裂。

備份檔範例 (2008-09-10 Customer 資料庫)

Customer_200809100000.BAK   -- 午夜完整備份
Customer_200809100100.TRN   -- 01:00 記錄備份
Customer_200809100130.TRN   -- 01:30 記錄備份
Customer_200809100200.TRN
Customer_200809100230.TRN
Customer_200809100300.TRN
Customer_200809100315.DIF   -- 03:15 差異備份

核心腳本邏輯 (簡化版)

DECLARE @BackupPath NVARCHAR(500) = 'D:\Backup\';
DECLARE @DBName NVARCHAR(128) = 'Customer';
DECLARE @Cmd NVARCHAR(MAX), @FileList TABLE (FileName NVARCHAR(256));

-- 1. 取得目錄檔案清單
SET @Cmd = 'dir /b ' + @BackupPath + @DBName + '_*.*';
INSERT @FileList EXEC master..xp_cmdshell @Cmd;

-- 2. 解析檔名、類型、時間，按時間排序
-- 3. 找出最新完整備份 (BAK)
-- 4. 找出該完整備份之後最新的差異備份 (DIF) (可選)
-- 5. 找出差異備份 (或完整備份) 之後所有記錄備份 (TRN)
-- 6. 動態產生 RESTORE 腳本：
--    RESTORE DATABASE @DBName FROM DISK = '...BAK' WITH NORECOVERY, REPLACE, MOVE ...
--    RESTORE DATABASE @DBName FROM DISK = '...DIF' WITH NORECOVERY, MOVE ...
--    RESTORE LOG @DBName FROM DISK = '...TRN' WITH NORECOVERY, MOVE ...
--    最後：RESTORE DATABASE @DBName WITH RECOVERY;

關鍵細節

• MOVE 子句將邏輯檔名對應至目標實例實體路徑 (可先查 RESTORE FILELISTONLY 取得)。
• 記錄備份必須**依序無縫套用**，腳本自動按時間排序保證順序。
• 若無差異備份，直接從完整備份接記錄備份。
• 腳本輸出為文字，DBA 可複製至 SSMS 執行或存檔備份。

擴展建議

• 整合 msdb.dbo.backupset 驗證 LSN 連續性，避免檔案命名錯誤導致還原失敗。
• 支援多資料庫批次產生、輸出至 SQL Agent 作業自動化。
• 加入郵件通知、錯誤處理、執行記錄表。

結語

自動掃描備份目錄產生還原腳本，將「手動拼湧 RESTORE 命令」轉為「可重複、可審計、可自動化」的標準流程，大幅降低 DBA 還原操作風險與時間成本。