konhon

-- Transfer對象的重要屬性

-- 1. 屬性

屬性名                            類型                描述
--------------------------------- ------------------- --------------------
CopyAllDefaults                Boolean    所有默認值
CopyAllObjects                 Boolean    所有對象
CopyAllRules                   Boolean    所有規則
CopyAllStoredProcedures        Boolean    所有存儲過程
CopyAllTables                  Boolean    所有表
CopyAllTriggers                Boolean    所有觸發器
CopyAllUserDefinedDatatypes    Boolean    所有用戶自定義類型
CopyAllViews                   Boolean    所有視圖
CopyData                       Boolean    所有數據
DestDatabase                   String     目標對象數據庫
DestLogin                      String     目標數據庫登陸用戶名
DestPassword                   String     目標數據庫登陸密碼
DestServer                     String     目標服務器
DestUseTrustedConnection       Boolean    用戶信任連接
DropDestObjectsFirst           Boolean    是否先刪除目標對象
IncludeDependencies            Boolean    是否包含依靠對象
ScriptType                     Boolean    腳本類型

-- 2. 重要方法: 

方法名稱                    功能描述
--------------------------- --------------------------
AddObject                   增加對象
AddObjectByName             通過對象名稱增加對象

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_CopyDB]
GO

/*-- 在 SQLServer 中使用SQLDMO.Transfer 實現數據遷移
    存儲過程實現源數據庫到目標數據庫的對象和數據的復制
    要求源數據庫和目標數據庫在同一服務器
    如果是要實現不同服務器之間的復制，則需要增加驗證信息
--鄒建 2005.07(引用請保留此信息)--*/

/*--調用示例

    CREATE DATABASE test
    EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test'
    DROP DATABASE test
--*/
CREATE PROCEDURE P_CopyDB     
@Des_DB      sysname,           --目標數據庫
@Obj_Type    nvarchar(4000)=N'',--復制的對象類型，可以是下列字符串列表：
                                -- O 所有對象，D 默認值，R 規則，P 存儲過程
                                -- T 表，TR 觸發器，DT 用戶定義數據類型
                                -- V 視圖，DATA 數據，DEL 刪除目標對象
@Source_DB   sysname=N'',       --源數據庫
@ServerName  sysname=N'',       --服務器名
@UserName    sysname=N'',       --用戶名，不指定則表示使用 Windows 身份登錄
@pwd         sysname=N''        --密碼 
AS
SET NOCOUNT ON
DECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int,
    @err int,@src varchar(255), @desc varchar(255)

IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAME
IF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME()

--創建sqldmo對象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err

--連接服務器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登錄
BEGIN
    EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
    IF @err<>0 GOTO lb_Err

    EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername
END
ELSE
    EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwd

IF @err<>0 GOTO lb_Err

--獲取數據庫集
EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT
IF @err<>0 GOTO lb_Err

--選擇源數據庫    
EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB
IF @err<>0 GOTO lb_Err

--選擇目標數據庫    
EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB
IF @err<>0 GOTO lb_Err

--設置復制的對象
EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT
IF @err<>0 GOTO lb_Err

--設置目標服務器信息
EXEC @err=sp_oasetproperty  @TransferID,'DestServer',@ServerName
IF @err<>0 GOTO lb_Err

  --設置連接用戶
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登錄
BEGIN
    EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1
    IF @err<>0 GOTO lb_Err
END
ELSE
BEGIN
    EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName
    IF @err<>0 GOTO lb_Err

    EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd
    IF @err<>0 GOTO lb_Err
END

  --設置復制對象信息
EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB
IF @err<>0 GOTO lb_Err

DECLARE tb CURSOR FAST_FORWARD LOCAL
FOR
SELECT Name FROM(
    SELECT KeyWord=N',D,',   Name=N'CopyAllDefaults' UNION ALL
    SELECT KeyWord=N',O,',   Name=N'CopyAllObjects' UNION ALL
    SELECT KeyWord=N',R,',   Name=N'CopyAllRules' UNION ALL
    SELECT KeyWord=N',P,',   Name=N'CopyAllStoredProcedures' UNION ALL
    SELECT KeyWord=N',T,',   Name=N'CopyAllTables' UNION ALL
    SELECT KeyWord=N',TR,',  Name=N'CopyAllTriggers' UNION ALL
    SELECT KeyWord=N',DT,',  Name=N'CopyAllUserDefinedDatatypes' UNION ALL
    SELECT KeyWord=N',V,',   Name=N'CopyAllViews' UNION ALL
    SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL
    SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst'
)A WHERE CHARINDEX(KeyWord,
        CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0
OPEN tb
FETCH tb INTO @src
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC @err=sp_oasetproperty @TransferID,@src,1
    IF @err<>0 GOTO lb_Err
    FETCH tb INTO @src
END
CLOSE tb
DEALLOCATE tb

--復制對象
EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID
IF @err<>0 GOTO lb_Err

--結束
SET @err=0
GOTO lb_Exit

--錯誤處理
lb_Err:
    EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT 
    RAISERROR(N'錯誤編號 %#x, 錯誤源 "%s", 錯誤描述 "%s"',16,1,@err,@src,@desc)
    RETURN -1

lb_Exit:
    EXEC sp_OADestroy @Dbid  
    EXEC sp_OADestroy @srvid 
    EXEC sp_OADestroy @TransferID 
    RETURN @err
GO