您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL Server 資料庫備份還原完整指南：T-SQL 腳本實戰與斷開連線技巧

編輯：MySQL 綜合教學

核心備份與還原語法

SQL Server 透過 BACKUP DATABASE 與 RESTORE DATABASE 實現檔案級備份還原，適用於單機遷移、災難恢復、環境複製等場景。

1. 完整備份腳本

-- 備份資料庫 mydb 到指定路徑
BACKUP DATABASE mydb
TO DISK = 'C:\DBBACK\mydb.BAK'
WITH FORMAT, INIT, NAME = 'mydb-Full Backup', STATS = 10;
-- 參數說明：
-- FORMAT: 覆寫現有媒體標頭
-- INIT: 覆寫現有備份集
-- STATS=10: 每 10% 顯示進度

關鍵注意事項：

目標資料夾 C:\DBBACK\ 必須預先存在，SQL Server 不會自動建立
備份檔名建議包含日期時間，如 mydb_20240115_1430.BAK
執行備份需 sysadmin 或 db_backupoperator 角色權限

2. 還原資料庫腳本

-- 切換至 master 避免鎖定目標資料庫
USE master;
-- 強制還原（覆蓋現有資料庫）
RESTORE DATABASE mydb
FROM DISK = 'C:\DBBACK\mydb.BAK'
WITH REPLACE,
     MOVE 'mydb' TO 'C:\Data\mydb.mdf',      -- 資料檔新路徑
     MOVE 'mydb_log' TO 'C:\Log\mydb_log.ldf', -- 日誌檔新路徑
     STATS = 10;

常見失敗原因與解決：

資料庫正在使用中： 還原要求獨佔存取，需先斷開所有連線
檔案路徑衝突： 使用 MOVE 子句指定新路徑
版本不相容： 無法將高版本備份還原到低版本 SQL Server

進階技巧：強制斷開連線並設為單用戶模式

當資料庫被佔用無法還原時，執行以下流程：

步驟 1：建立 Kill 連線預存程序

CREATE PROC sp_KillDbConnections @DBName sysname
AS
BEGIN
    DECLARE @spid INT, @sql NVARCHAR(MAX) = N'';
    SELECT @sql = @sql + N'KILL ' + CAST(spid AS NVARCHAR) + N'; '
    FROM sys.sysprocesses
    WHERE dbid = DB_ID(@DBName) AND spid != @@SPID;
    EXEC sp_executesql @sql;
END;

步驟 2：執行斷開並設為單用戶

-- 1. 殺掉所有連線
EXEC sp_KillDbConnections 'mydb';
-- 2. 設為單用戶模式（立即生效，ROLLBACK IMMEDIATE 強制回滾未完成事務）
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- 3. 執行還原（此時擁有獨佔權限）
RESTORE DATABASE mydb FROM DISK = 'C:\DBBACK\mydb.BAK' WITH REPLACE, ...;
-- 4. 還原完成後恢復多用戶模式
ALTER DATABASE mydb SET MULTI_USER;

備份策略最佳實踐

策略	頻率	優點	缺點
完整備份	每日/每週	還原最簡單、最快	耗時長、佔空間大
差異備份	每小時/每日	僅備份變更頁、較小	還原需完整+最新差異
交易記錄備份	每 15-60 分鐘	可時間點恢復、RPO 近乎為零	需完整復原模式、管理複雜

驗證備份可用性（關鍵步驟）

-- 僅驗證備份檔完整性，不還原
RESTORE VERIFYONLY FROM DISK = 'C:\DBBACK\mydb.BAK';
-- 查看備份集詳細資訊
RESTORE HEADERONLY FROM DISK = 'C:\DBBACK\mydb.BAK';
-- 查看檔案清單（還原時需 MOVE 的邏輯名）
RESTORE FILELISTONLY FROM DISK = 'C:\DBBACK\mydb.BAK';

自動化備份腳本範例 (SQL Server Agent 作業)

DECLARE @path NVARCHAR(256) = N'C:\DBBACK\';
DECLARE @dbName NVARCHAR(128) = N'mydb';
DECLARE @fileName NVARCHAR(256) = @path + @dbName + N'_' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N'_' + REPLACE(CONVERT(NVARCHAR(5), GETDATE(), 108), ':', '') + N'.BAK';

BACKUP DATABASE @dbName TO DISK = @fileName
WITH COMPRESSION, CHECKSUM, STATS = 10;