SQL-DMO Transfer 物件實戰：自動化資料庫結構升級與部署

背景痛點

專案升級時，資料庫結構變更（表、檢視、預存程序、觸發器）若靠手工腳本極易遺漏錯誤。syscomments 儲存的腳本受限於 8000 字元，且換行符處理棘手。SQL-DMO Transfer 物件提供記憶體級腳本生成與跨資料庫部署能力，完美解決此問題。

Transfer 核心屬性

核心方法

• AddObject / AddObjectByName：指定要轉移的特定物件。
• ScriptTransfer：產生腳本字串（可存檔或直接執行）。
• TransferData：執行實際轉移。

範例：以來源資料庫為藍本升級目標資料庫

CREATE PROCEDURE P_UDB
    @Source_DB sysname,
    @Dest_DB sysname,
    @Dest_Server sysname = NULL,  -- NULL 表本機
    @Dest_Login sysname = 'sa',
    @Dest_Pwd sysname = ''
AS
BEGIN
    DECLARE @oTransfer INT, @hr INT
    EXEC @hr = sp_OACreate 'SQLDMO.Transfer', @oTransfer OUT
    IF @hr <> 0 GOTO ErrHandler

    EXEC @hr = sp_OASetProperty @oTransfer, 'CopyAllObjects', 1
    EXEC @hr = sp_OASetProperty @oTransfer, 'CopySchema', 1
    EXEC @hr = sp_OASetProperty @oTransfer, 'CopyData', 0
    EXEC @hr = sp_OASetProperty @oTransfer, 'DropDestObjectsFirst', 1
    EXEC @hr = sp_OASetProperty @oTransfer, 'IncludeDependencies', 1

    EXEC @hr = sp_OASetProperty @oTransfer, 'SrcServer', @@SERVERNAME
    EXEC @hr = sp_OASetProperty @oTransfer, 'SrcDatabase', @Source_DB
    EXEC @hr = sp_OASetProperty @oTransfer, 'SrcLogin', @Dest_Login
    EXEC @hr = sp_OASetProperty @oTransfer, 'SrcPassword', @Dest_Pwd

    EXEC @hr = sp_OASetProperty @oTransfer, 'DestServer', ISNULL(@Dest_Server, @@SERVERNAME)
    EXEC @hr = sp_OASetProperty @oTransfer, 'DestDatabase', @Dest_DB
    EXEC @hr = sp_OASetProperty @oTransfer, 'DestLogin', @Dest_Login
    EXEC @hr = sp_OASetProperty @oTransfer, 'DestPassword', @Dest_Pwd

    EXEC @hr = sp_OAMethod @oTransfer, 'TransferData'
    IF @hr <> 0 GOTO ErrHandler

    EXEC sp_OADestroy @oTransfer
    RETURN 0

ErrHandler:
    DECLARE @errMsg VARCHAR(255)
    EXEC sp_OAGetErrorInfo @oTransfer, @errMsg OUT
    RAISERROR('SQL-DMO Transfer 失敗: %s', 16, 1, @errMsg)
    EXEC sp_OADestroy @oTransfer
    RETURN -1
END

注意事項

• SQL-DMO 已在 SQL Server 2012 移除，新專案請改用 SMO (SQL Server Management Objects) 或 SqlPackage (DacFx)。
• 執行權限：需 sysadmin 或足夠的 CREATE 權限。
• 大量物件轉移時建議分批，避免記憶體壓力。