sql server 复制数据库 存储过程

黄智勇

　　/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移
　　存储过程实现源数据库到目标数据库的对象和数据的复制
　　要求源数据库和目标数据库在同一服务器
　　如果是要实现不同服务器之间的复制，则需要增加验证信息
　　--邹建 2005.07(引用请保留此信息)--*/
　　/*--调用示例

　　>　　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 @err0 GOTO lb_Err
　　--连接服务器
　　IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
　　BEGIN
　　EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
　　IF @err0 GOTO lb_Err
　　EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername
　　END
　　ELSE
　　EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwd
　　IF @err0 GOTO lb_Err
　　--获取数据库集
　　EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT
　　IF @err0 GOTO lb_Err
　　--选择源数据库
　　EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB
　　IF @err0 GOTO lb_Err
　　--选择目标数据库
　　EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB
　　IF @err0 GOTO lb_Err
　　--设置复制的对象
　　EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT
　　IF @err0 GOTO lb_Err
　　--设置目标服务器信息
　　EXEC @err=sp_oasetproperty  @TransferID,'DestServer',@ServerName
　　IF @err0 GOTO lb_Err
　　--设置连接用户
　　IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
　　BEGIN
　　EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1
　　IF @err0 GOTO lb_Err
　　END
　　ELSE
　　BEGIN
　　EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName
　　IF @err0 GOTO lb_Err
　　EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd
　　IF @err0 GOTO lb_Err
　　END
　　--设置复制对象信息
　　EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB
　　IF @err0 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 @err0 GOTO lb_Err
　　FETCH tb INTO @src
　　END
　　CLOSE tb
　　DEALLOCATE tb
　　--复制对象
　　EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID
　　IF @err0 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