SQL Server 資料與記錄檔分離儲存：備份還原實戰腳本

核心原則

資料檔 (.mdf/.ndf) 與記錄檔 (.ldf) 操作產生大量 I/O，**分離至不同物理磁碟/RAID 陣列**可：

• 分散 I/O 負載，提升讀寫平行度
• 記錄檔獨享順序寫入頻道，減少延遲
• 災難恢復時，若資料磁碟損壞，記錄檔仍可支援尾部記錄備份，將資料損失降至零

備份與還原標準 T-SQL 腳本

完整備份

-- 確保備份目錄已存在
BACKUP DATABASE MyDB
TO DISK = 'D:\DBBackup\MyDB_Full_20240115.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 5
-- INIT: 覆寫既有備份組
-- COMPRESSION: 壓縮備份 (SQL 2008+)
-- CHECKSUM: 頁面校驗和
-- STATS: 進度報告間隔

差異備份 (配合完整備份)

BACKUP DATABASE MyDB
TO DISK = 'D:\DBBackup\MyDB_Diff_20240115_1400.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 5

記錄檔備份 (完整/大量記錄模式必備)

BACKUP LOG MyDB
TO DISK = 'D:\DBBackup\MyDB_Log_20240115_1430.trn'
WITH COMPRESSION, STATS = 5

還原流程 (含異動記錄點對點恢復)

-- 1. 尾部記錄備份 (若記錄檔磁碟正常)
BACKUP LOG MyDB TO DISK = 'D:\DBBackup\MyDB_TailLog.trn' WITH NORECOVERY

-- 2. 切換單用戶模式斷開連線
USE master
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- 3. 還原完整備份 (NORECOVERY 保留後續還原)
RESTORE DATABASE MyDB
FROM DISK = 'D:\DBBackup\MyDB_Full_20240115.bak'
WITH NORECOVERY, REPLACE, STATS = 5,
MOVE 'MyDB_Data' TO 'E:\SQLData\MyDB.mdf',      -- 重新定位資料檔
MOVE 'MyDB_Log'  TO 'F:\SQLLog\MyDB.ldf'        -- 重新定位記錄檔 (分離儲存)

-- 4. 還原差異備份 (若有)
RESTORE DATABASE MyDB
FROM DISK = 'D:\DBBackup\MyDB_Diff_20240115_1400.bak'
WITH NORECOVERY, STATS = 5

-- 5. 依序還原記錄檔備份
RESTORE LOG MyDB FROM DISK = 'D:\DBBackup\MyDB_Log_20240115_1430.trn' WITH NORECOVERY
-- ... 更多記錄檔 ...

-- 6. 還原尾部記錄 (若步驟 1 成功)
RESTORE LOG MyDB FROM DISK = 'D:\DBBackup\MyDB_TailLog.trn' WITH NORECOVERY

-- 7. 恢復資料庫上線
RESTORE DATABASE MyDB WITH RECOVERY

-- 8. 恢復多用戶模式
ALTER DATABASE MyDB SET MULTI_USER

強制斷開連線輔助預存程序

CREATE PROC KillSpid(@DBName sysname)
AS
BEGIN
    DECLARE @spid int, @sql nvarchar(max)
    
    -- 動態建立游標殺掉所有連線
    SET @sql = N'DECLARE c CURSOR FOR SELECT spid FROM sysprocesses WHERE dbid = DB_ID(''' + @DBName + ''')'
    EXEC sp_executesql @sql
    
    DECLARE c CURSOR FOR SELECT spid FROM sysprocesses WHERE dbid = DB_ID(@DBName)
    OPEN c
    FETCH NEXT FROM c INTO @spid
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC ('KILL ' + @spid)
        FETCH NEXT FROM c INTO @spid
    END
    CLOSE c
    DEALLOCATE c
END
GO

-- 使用範例
EXEC KillSpid 'MyDB'
-- 緊接著 ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

最佳實踐檢核表

總結

資料與記錄檔分離儲存是 SQL Server 架構設計的基石。配合標準化備份/還原腳本、尾部記錄備份、單用戶切換與連線清理流程，可構建 RPO 接近零、RTO 可控的災難恢復能力。