SQL Server 資料庫備份還原兩大法：圖形化操作與 T-SQL 腳本完整對照

資料庫備份還原是 DBA 核心基本功。本文對比 SSMS 圖形化介面與 T-SQL 腳本兩種方式，並給出生產環境可直接套用的參數化還原腳本模板。

方式一：SSMS 圖形化介面（適合單次、臨時操作）

1. 右鍵「資料庫」→「還原資料庫...」
2. 來源：選「裝置」→ 加入 .bak 檔案。
3. 目的地：輸入新資料庫名稱（如 DBTest）。
4. 關鍵：選項頁面 → 重新定位資料庫檔案，指定 .mdf、.ldf 新路徑，避免覆蓋原庫。
5. 勾選「取代現有資料庫 (WITH REPLACE)」。

方式二：T-SQL 腳本（適合自動化、排程、版控）

/* 參數區 ---*/
DECLARE @SrcDb SYSNAME = 'DB';           -- 來源資料庫
DECLARE @BakPath NVARCHAR(260) = 'E:\DBTest.bak';
DECLARE @TargetDb SYSNAME = 'DBTest';    -- 目標資料庫
DECLARE @DataPath NVARCHAR(260) = 'E:\MSSQL\Data\';
DECLARE @LogPath  NVARCHAR(260) = 'E:\MSSQL\Log\';
/* ----------*/

-- 1. 完整備份
BACKUP DATABASE @SrcDb TO DISK = @BakPath
WITH INIT, COMPRESSION, STATS = 10;

-- 2. 檢視備份檔邏輯檔名（還原時需用）
RESTORE FILELISTONLY FROM DISK = @BakPath;

-- 3. 還原為新資料庫（假設邏輯檔名為 DBTest / DBTest_log）
RESTORE DATABASE @TargetDb FROM DISK = @BakPath
WITH 
    MOVE 'DBTest'     TO @DataPath + @TargetDb + '.mdf',
    MOVE 'DBTest_log' TO @LogPath  + @TargetDb + '_log.ldf',
    STATS = 10,          -- 每 10% 回報進度
    REPLACE;             -- 強制覆蓋同名資料庫

關鍵參數解析

進階：自動取得邏輯檔名的動態腳本

DECLARE @Sql NVARCHAR(MAX) = N'';
SELECT @Sql += N'
RESTORE DATABASE ' + QUOTENAME(@TargetDb) + N' FROM DISK = ' + QUOTENAME(@BakPath, '''') + N'
WITH '
    + STRING_AGG(
        CASE type WHEN 'D' 
            THEN N'MOVE ' + QUOTENAME(logical_name) + N' TO ' + QUOTENAME(@DataPath + @TargetDb + '.mdf', '''')
            ELSE N'MOVE ' + QUOTENAME(logical_name) + N' TO ' + QUOTENAME(@LogPath + @TargetDb + '_log.ldf', '''')
        END, N', ')
    + N', STATS = 10, REPLACE;'
FROM (
    SELECT logical_name, type
    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 
        'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BakPath, ''''))
) AS f;
EXEC sp_executesql @Sql;

註：需啟用 Ad Hoc Distributed Queries 或改用臨時表接收 FILELISTONLY 結果。

生產環境檢查清單

• [ ] 備份檔完整性驗證：RESTORE VERIFYONLY FROM DISK = ...
• [ ] 目標磁碟空間 ≥ 資料庫大小 × 1.2
• [ ] 還原後執行 DBCC CHECKDB 確保一致性
• [ ] 更新統計資料：EXEC sp_updatestats
• [ ] 修正孤立使用者：EXEC sp_change_users_login 'Auto_Fix', 'user'

熟練掌握 T-SQL 還原腳本，即可將備份還原納入 CI/CD 流程，實現資料庫版本化部署與災難演練自動化。