SQL Server 數據庫備份還原五大核心操作實戰：完全備份、還原、收縮、轉移、附加脫離

操作一：完全備份（GUI 與 T-SQL 雙模式）

SSMS 圖形界面

1. 對象資源管理器 → 數據庫 → 右鍵目標庫 → 任務 → 備份。
2. 備份類型選「完整」，目標選「磁盤」，添加路徑如 D:\Backup\Finance_Full_20240115.bak。
3. 選項頁：勾選「驗證備份完成」、「檢查和校驗和」，壓縮選「壓縮備份」。

T-SQL 腳本（便於自動化作業）

BACKUP DATABASE Finance
TO DISK = 'D:\Backup\Finance_Full_20240115.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10, INIT;
GO

操作二：還原數據庫（異機還原重點）

關鍵步驟：文件重定位

RESTORE DATABASE Finance
FROM DISK = 'D:\Backup\Finance_Full_20240115.bak'
WITH 
    REPLACE,                              -- 強制覆蓋現有庫
    RECOVERY,                             -- 完成後可用（默認）
    STATS = 10,
    MOVE 'Finance_Data' TO 'D:\MSSQL\Data\Finance.mdf',   -- 邏輯名→新物理路徑
    MOVE 'Finance_Log'  TO 'L:\MSSQL\Log\Finance.ldf';    -- 數據/日志分盤提升 IO
GO

查看備份文件邏輯名： RESTORE FILELISTONLY FROM DISK = '...bak'

操作三：收縮數據庫與日志（謹慎使用）

正確流程：簡單恢復模式下收縮日志

-- 1. 切換簡單模式（斷開日志鏈，需確認不影響備份策略）
ALTER DATABASE Finance SET RECOVERY SIMPLE;
GO

-- 2. 收縮日志文件至目標大小（MB）
DBCC SHRINKFILE (Finance_Log, 500);
GO

-- 3. 恢復完整模式並立即做全備（重建日志鏈）
ALTER DATABASE Finance SET RECOVERY FULL;
BACKUP DATABASE Finance TO DISK = 'D:\Backup\Finance_Full_AfterShrink.bak' WITH COMPRESSION;
GO

⚠️ 收縮數據文件通常不推薦：導致索引碎片、後續業務寫入又需擴展，反覆震蕩。僅在一次性大量刪除數據後、確不再增長時考慮。

操作四：數據庫轉移（生產→測試/遷機房）

方案 A：備份還原（首選，最快最全）

同上操作一二，異機只需複製 .bak 文件。

方案 B：生成腳本 + 導入導出（無備份權限/跨版本降級）

1. 任務 → 生成腳本 → 選「架構+數據」，勾選「腳本索引/主鍵/外鍵/約束」。
2. 目標庫執行腳本建架構。
3. 任務 → 導入數據 → 選源/目標 → 逐表導入數據。

操作五：附加/脫離文件快速遷移（停機窗口短）

-- 源服務器：脫離
USE master;
ALTER DATABASE Finance SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'Finance';
GO
-- 複製 .mdf .ldf 到目標服務器同路徑或新路徑

-- 目標服務器：附加
CREATE DATABASE Finance
ON (FILENAME = 'D:\MSSQL\Data\Finance.mdf'),
   (FILENAME = 'L:\MSSQL\Log\Finance.ldf')
FOR ATTACH;
GO

⚠️ 附加會斷開復制/鏡像/CDC 等配置，遷移前腳本化記錄相關設置。

避坑清單

• 還原報「獨佔鎖失敗」→ ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE 踢掉連接。
• 路徑不存在 → 提前建立目錄，確保 SQL 服務賬號寫入權限。
• 版本不兼容 → 低版本備份可還原高版本，高版本不可還原低版本。
• 備份鏈斷裂 → 全備後必須接日志備份，收縮日志後必須立即全備。