SQL Server 復製表結構與物件的通用方案：Transfer 物件與腳本生成

在資料庫版本升級、環境遷移、開發/測試/生產同步場景中，需將表結構、索引、觸發器、預存程序、檢視表、預設值、規則、使用者定義型別等物件完整複製。SQL Server 提供 SQL-DMO (SQL 2000) / SMO (SQL 2005+) 物件模型中的 Transfer 類別，支援程式化控制複製行為。本文詳細說明核心屬性、方法與使用模式。

Transfer 物件核心屬性 (控制「複製什麼」)

核心方法

• AddObject(ByVal Object As Object)：加入單一物件 (需先取得物件參考)
• AddObjectByName(ByVal Name As String, ByVal Type As ObjectType)：依名稱加入物件
• ScriptTransfer()：生成腳本字串 (不執行，供審查/存檔)
• TransferData()：直接執行複製至目標資料庫

SMO (SQL 2005+) C# 完整範例

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

var sourceConn = new ServerConnection("SourceServer", "login", "pwd");
var sourceSrv = new Server(sourceConn);
var sourceDb = sourceSrv.Databases["SourceDB"];

var destConn = new ServerConnection("DestServer", "login", "pwd");
var destSrv = new Server(destConn);
var destDb = destSrv.Databases["DestDB"]; // 需預先建立

var transfer = new Transfer(sourceDb)
{
    CopyAllTables = true,
    CopyAllViews = true,
    CopyAllStoredProcedures = true,
    CopyAllTriggers = true,
    CopyAllDefaults = true,
    CopyAllRules = true,
    CopyAllUserDefinedDatatypes = true,
    CopyData = false, // 只複製結構
    DropDestObjectsFirst = true,
    IncludeDependencies = true,
    DestinationServer = destSrv.Name,
    DestinationDatabase = destDb.Name,
    DestinationLogin = "login",
    DestinationPassword = "pwd",
    DestinationLoginSecure = false
};

// 選項 1: 直接執行複製
transfer.TransferData();

// 選項 2: 生成腳本審查後再執行
var script = transfer.ScriptTransfer();
// File.WriteAllText("SyncSchema.sql", script);
// 然後在目標端執行 script

PowerShell 版本 (生產自動化首選)

Import-Module SqlServer

$src = New-Object Microsoft.SqlServer.Management.Smo.Server("SourceServer")
$srcDb = $src.Databases["SourceDB"]

$dest = New-Object Microsoft.SqlServer.Management.Smo.Server("DestServer")
$destDb = $dest.Databases["DestDB"]

$transfer = New-Object Microsoft.SqlServer.Management.Smo.Transfer($srcDb)
$transfer.CopyAllObjects = $true
$transfer.CopyData = $false
$transfer.DropDestObjectsFirst = $true
$transfer.DestinationServer = $dest.Name
$transfer.DestinationDatabase = $destDb.Name
$transfer.DestinationLogin = "login"
$transfer.DestinationPassword = "pwd"

# 生成腳本檔
$script = $transfer.ScriptTransfer()
$script | Out-File "C:\Deploy\SyncSchema_$(Get-Date -Format yyyyMMdd).sql" -Encoding UTF8

# 或直接執行
# $transfer.TransferData()

進階場景與注意事項

1. 部分物件複製： 設 CopyAllObjects=$false，改用 AddObjectByName 精確控制
2. 跨版本遷移： SMO 向下相容，高版本 SMO 可操作低版本引擎，但目標端不支援的語法 (如 FILESTREAM, COLUMNSTORE) 會報錯，需預先相容性檢查
3. 大型資料庫： TransferData 單執行緒、無進度、易逾時。生產建議：生成腳本 → 分批執行 → 並行索引建立 → 驗證行數
4. 權限/擁有者同步： Transfer 不複製權限。需額外腳本同步 GRANT/DENY、ALTER AUTHORIZATION
5. 加密物件： WITH ENCRYPTION 的 SP/檢視/觸發器無法腳本化，需解密或排除

替代方案對比

結語

Transfer 物件是快速原型、腳本生成、中小型環境同步的利器。但對於 **生產級持續交付**，強烈建議採用 **SSDT (SQL Server Data Tools) + DACPAC + SQLPackage** 的狀態式部署模式，將 Schema 視為程式碼納入 Git 版控，實現真正的 Database DevOps。