SQL Server 資料遷移的六大實戰方法完整指南

在 SQL Server 環境中進行資料遷移是資料庫管理員（DBA）與開發人員常遇到的核心任務。無論是伺服器升級、環境遷移、還是資料同步，選擇正確的工具與策略能大幅降低風險與停機時間。本文整理六種主流遷移方案，從圖形化精靈到底層指令、從離線備份到線上複寫，助您根據業務場景做出最佳決策。

一、 善用 DTS（Data Transformation Services）視覺化設計器

DTS 是 SQL Server 7.0/2000 時代內建的 ETL 工具，雖然後來被 SSIS 取代，但在舊版本環境中仍具高度實用性。其最大優勢在於圖形化介面、支援多任務流程控制、且可儲存為封裝（Package）重複執行。

操作要點

• 在 Enterprise Manager 中右鍵資料庫 →「所有工作」→「匯入/匯出資料」啟動精靈。
• 支援異構資料源（Oracle、Access、文字檔、Excel 等）互相複製。
• 可另存為 .dts 檔或存入 msdb，排程自動化執行。

避坑指南

外鍵與索引順序極敏感：建議先匯入無相依性的主檔表，再匯入明細表；或一次性全庫匯入後，以腳本重建 FK、PK、索引。另一最佳實務是將 DDL（建表、索引、FK）與 DML（資料）腳本分離，並將資料檔、記錄檔置於不同磁碟，提升 I/O 效能。

二、 BCP（Bulk Copy Program）高效能命令列工具

雖然 Microsoft 在 SQL Server 7.0 後不再推薦 BCP 為首選，但資深 DBA 依偏愛其無圖形介面負擔、記憶體佔用極低、大量資料吞吐速度極快的特性。

• 適用場景：純文字檔（CSV、固定寬度）與 SQL Server 資料表/檢視表雙向匯出入。
• 缺點：僅支援單表操作、錯誤處理需自行撰寫腳本、格式化檔（.fmt）維護成本較高。

三、 離線備份與還原——最完整的「整庫搬家」

若目標是完整複製整個資料庫（含結構、資料、索引、觸發器、權限、系統目錄），備份/還原是最省時、最不易出錯的方式。

1. 來源端執行「完整備份」至磁碟裝置（.bak）。
2. 將 .bak 複製至目標伺服器本地磁碟（網路路徑還原較慢且易中斷）。
3. 目標端建立同名資料庫 → 還原時勾選「強制還原覆寫現有資料庫」與「從裝置還原」。

此法自動保留所有 FK、PK、索引、統計資料，零手工修正。

四、 直接拷貝 MDF/LDF 檔案並附加（Attach）

適用於同版本、同版次、相容等級相同的伺服器間快速移動。步驟如下：

1. 來源端執行 sp_detach_db 'DBName' 分離資料庫（或停止 SQL Server 服務後直接複製檔案）。
2. 將 .mdf、.ldf 複製至目標伺服器指定路徑。
3. 目標端執行附加指令：

EXEC sp_attach_db @dbname = 'TestDB',
    @filename1 = 'D:\MSSQL\Data\TestDB.mdf',
    @filename2 = 'D:\MSSQL\Log\TestDB_log.ldf';

單一檔案附加（僅保留資料檔，由 SQL Server 自動重建空白記錄檔，原記錄內容遺失）：

EXEC sp_detach_db @dbname = 'TestDB';
EXEC sp_attach_single_file_db @dbname = 'TestDB',
    @physname = 'D:\MSSQL\Data\TestDB.mdf';

五、 應用層自訂腳本（INSERT…SELECT / SELECT…INTO）

在 PB、VB、.NET 或 Query Analyzer 中撰寫 T-SQL 腳本，彈性最高，適合增量同步、條件式遷移、跨伺服器連結伺服器場景。

-- 建新表並寫入資料
SELECT col1, col2 INTO NewTable FROM SourceDB.dbo.SourceTable WHERE ...;

-- 寫入既有表
INSERT INTO TargetTable (col1, col2)
SELECT col1, col2 FROM SourceDB.dbo.SourceTable WHERE ...;

注意：跨伺服器需啟用「分散式交易協調器 (MSDTC)」，且網路頻寬、延遲直接影響效能。

六、 SQL Server 複寫——企業級持續同步方案

複寫（快照式、交易式、合併式）提供近即時、雙向、可過濾的資料同步能力，但架構複雜，部署前必須滿足下列先決條件：

1. SQL Server Agent 與 MSDTC 服務均已啟動。
2. 所有發行文章（Article）的資料表必須擁有主鍵。
3. 含 text/image 欄位時，發行集屬性需勾選 WITH LOG；max text repl size 需大於最大欄位長度。
4. 發行者/散發者/訂閱者機器需開啟管理共享（C$、D$…）。
5. SQL Server Agent 服務帳戶不可為 Local System，需使用具網路存取權的網域帳戶。
6. 跨網域環境需建立雙向信任關係。

複寫適用於「多地異構報表、異地災備、行動裝置離線同步」等進階場景，建議先於測試環境完整演練拓撲與失效切換流程。

總結與選型建議

實務上常採「混合策略」：先用備份/還原建立基線，再以複寫或腳本處理增量；或以 DTS/SSIS 進行 ETL 轉換後載入。掌握每種工具的邊界與優缺點，才能在停機窗口、資料一致性、維運成本三者間取得最佳平衡。