SQL Server 資料庫跨伺服器遷移完整指南：備份還原、分離附加、導入導出三大法

前言

SQL Server 資料庫遷移是 DBA 核心技能。本文詳細說明三大主流遷移方法：**備份還原 (最通用)**、**分離附加 (最快、需停機)**、**導入導出 (跨版本、可選擇性遷移)**，並涵蓋登入遷移、孤立用戶修復、作業/警示/SSIS 遷移等完整清單。

方法一：備份還原 — 通用、支援線上、可 PITR

適用場景

• 跨版本升級 (2008R2 → 2019、2017 → 2022)
• 跨架構 (x86 → x64)
• 需保持來源資料庫線上服務 (配合記錄備份)
• 異地遷移 (備份檔透過網路/物件儲存傳輸)

標準流程

1. 完整備份 (來源)：

   BACKUP DATABASE [MyDB] TO DISK = 'N:\Backup\MyDB_FULL.bak'
   WITH COMPRESSION, CHECKSUM, STATS = 5;

2. 傳輸備份檔 至目標伺服器 (robocopy / scp / S3)
3. 還原完整備份 (目標，NORECOVERY)：

   RESTORE DATABASE [MyDB] FROM DISK = 'N:\Backup\MyDB_FULL.bak'
   WITH NORECOVERY,
   MOVE 'MyDB' TO 'D:\Data\MyDB.mdf',
   MOVE 'MyDB_Log' TO 'L:\Log\MyDB_Log.ldf',
   STATS = 5;

4. 記錄備份 (來源，停止寫入前)：

   BACKUP LOG [MyDB] TO DISK = 'N:\Backup\MyDB_LOG.trn'
   WITH COMPRESSION, CHECKSUM;

5. 還原記錄備份 (目標，RECOVERY)：

   RESTORE LOG [MyDB] FROM DISK = 'N:\Backup\MyDB_LOG.trn'
   WITH RECOVERY;

6. 驗證：DBCC CHECKDB、核心查詢煙測

關鍵選項說明

方法二：分離附加 — 極速、需離線、同版本/相容版本

限制

• 來源資料庫**必須離線** (分離期間不可存取)
• 目標版本**相同或更高** (不支援降級)
• 排序規則必須相容 (SQL 2000 間需相同；2005+ 保留來源排序規則)

流程

1. 分離 (來源)：

   EXEC sp_detach_db @dbname = 'MyDB', @skipchecks = 'true'; -- skipchecks 加速

2. 複製 .mdf、.ndf、.ldf 至目標伺服器對應路徑
3. 附加 (目標)：

   EXEC sp_attach_db @dbname = 'MyDB',
       @filename1 = 'D:\Data\MyDB.mdf',
       @filename2 = 'L:\Log\MyDB_Log.ldf';

   或單檔附加 (無記錄檔，自動重建)：

   EXEC sp_attach_single_file_db @dbname = 'MyDB',
       @physname = 'D:\Data\MyDB.mdf';

方法三：導入導出精靈 / SSIS / bcp — 可選擇性遷移、跨版本最穩

優勢

• 可僅遷移特定表/檢視/預存程序
• 來源/目標版本、排序規則**完全無關**
• 目標資料庫體積較小 (不含未使用空間、碎片)

工具選擇

• SSMS 導入導出精靈：圖形化、適合一次性小量
• SSIS (Integration Services)：企業級 ETL、可排程、錯誤處理、資料轉換
• bcp (Bulk Copy Program)：命令列、最高效能大量匯出入、適合自動化腳本

bcp 範例

-- 匯出
bcp MyDB.dbo.BigTable out BigTable.dat -S SourceServer -U user -P pass -c -t, -r\n

-- 匯入 (目標表需存在)
bcp MyDB.dbo.BigTable in BigTable.dat -S TargetServer -U user -P pass -c -t, -r\n -b 10000 -h "TABLOCK"

遷移後必做：登入與權限同步

1. 腳本化登入 (含 SID、密碼雜湊)

-- 來源執行 (sp_help_revlogin 微軟官方腳本)
EXEC sp_help_revlogin;
-- 複製輸出至目標執行，建立相同 SID 的登入

2. 修復孤立用戶

-- 目標資料庫執行
EXEC sp_change_users_login 'Report';  -- 列出孤立用戶

-- 修復標準登入
EXEC sp_change_users_login 'Auto_Fix', 'app_user', NULL, 'NewPass!';

-- 修復 dbo 擁有者
EXEC sp_changedbowner 'sa';

3. 同步預設資料庫

ALTER LOGIN app_user WITH DEFAULT_DATABASE = MyDB;

遷移輔助組件清單

排序規則 (Collation) 遷移陷阱

• SQL 2000 → 2000：來源/目標 Collation 必須完全相同
• SQL 2000 → 2005+ / 2005+ 之間：資料庫保留來源 Collation，但 tempdb 為目標實例 Collation → 臨時表聯接可能衝突
• 解決：目標實例安裝時指定相同 Collation，或資料庫層級 ALTER DATABASE COLLATE (需離線、重建所有索引/統計)

遷移核對清單 (上線前必驗)

1. 資料列數核對：SELECT COUNT(*) FROM src.tbl EXCEPT SELECT COUNT(*) FROM dst.tbl
2. 核心業務查詢效能基線對比
3. DBCC CHECKDB 無錯誤
4. 登入/用戶/權限完整對應
5. Agent 作業啟用、排程正確
6. 連結伺服器連通性
7. 備份作業排程已建立
8. 監控告警已配置

總結建議

遷移不只是「搬資料」，更是「驗證、同步、監控」的系統工程。請依清單逐項核對，確保零驚喜上線。