SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.spCopyDB    Script Date: 07/11/2006 17:18:56 ******/
alter  procedure spDBTransfer
@strServer   VARCHAR(100),--the name of the source server
@strFromDB   VARCHAR(100),--the name of the source Database
@SourceUID   VARCHAR(100)=null,--the Login to the source server
@SourcePWD   VARCHAR(100)=null,--the password to the source server
@strToServer VARCHAR(100),--the name of the Destination server
@strDestDB   VARCHAR(100),--the name of the Destination Database
@strToUser   VARCHAR(100),--the Login to the Destination Server
@strToPassword   VARCHAR(100),--the password to the Destination Server
@strScriptsDir VARCHAR(100),--the directory for transferring scripts
@strTablesTocopy varchar(255)=null,--comma-delimited list of tables to copy
@strProceduresToCopy varchar(255)=null,--comma-delimited list of procedures to copy
@strFunctionsToCopy varchar(255)=null--comma-delimited list of functions to copy
/*
--if individual objects are not specified, then all of the Database are copied!
--replace the userid and password with default if using windows authentication
--copy the PUBS database from MySourceServer to MyDestinationServer.DestinationDatabase
spDBTransfer 'MySourceServer','pubs','sa','secretPassword',
		'MyDestinationServer','DestinationDatabase','sa','secretPassword','c:\temp'

spDBTransfer 'MySourceServer','pubs','sa','secretPassword',
		'MyDestinationServer','DestinationDatabase','sa','secretPassword','c:\temp',
		'dbo.employee'

spDBTransfer 'MySourceServer','pubs','sa','secretPassword',
		'MyDestinationServer','DestinationDatabase','sa','secretPassword','c:\temp',
		@strTablesTocopy='dbo.employee,dbo.jobs'

spDBTransfer 'MySourceServer','waffle','sa','secretPassword',
		'MyDestinationServer','DestinationDatabase','sa','secretPassword','c:\temp',
		@strProceduresTocopy='dbo.spDBTransfer'

spDBTransfer 'MySourceServer','waffle','sa','secretPassword'
		,'MyDestinationServer','DestinationDatabase','sa','secretPassword','c:\temp',
 		@strFunctionsTocopy='dbo.InitCap'


--the destination database must already exist.
*/
as
Declare @hr int,
@oServer int,
@oTransfer int,
@ErrorObject int,
@strCommand varchar(255),
@strResult varchar(255),
@Errormessage varchar(2000),
@strObjectName Varchar(100),
@slice int

    exec @hr = sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
if @hr=0 exec @hr = sp_OACreate 'SQLDMO.Transfer', @oTransfer OUT
if @SourcePWD is null or @SourceUID is null
	begin
	--use a trusted connection 
	if @hr=0 Select @ErrorMessage='Setting login to windows authentication on '+@strServer, @ErrorObject=@oServer
	if @hr=0 exec @hr = sp_OASetProperty @oServer, 'LoginSecure', 1
	if @hr=0 Select @ErrorMessage='logging in to the requested server using windows authentication on '+@strServer
	if @SourceUID is null and @hr=0 exec @hr = sp_OAMethod @oServer, 'Connect', NULL, @strServer 
	if @SourceUID is not null and @hr=0 exec @hr = sp_OAMethod @oServer, 'Connect', NULL, @strServer ,@SourceUID
	end
else
	Begin
	if @hr=0 SELECT @ErrorMessage = 'Connecting to '''+@strServer+
		''' with user ID '''+@SourceUID+'''', @ErrorObject=@oServer
	if @hr=0 exec @hr = sp_OAMethod @oServer, 'Connect', NULL, @strServer ,
						 @SourceUID , @SourcePWD
	end
if @hr=0 Select @errorObject=@oTransfer,
		@errorMessage='assigning values to parameters'
if coalesce(@strTablesTocopy,@strProceduresToCopy,@strFunctionsToCopy) is null
	begin
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllObjects', 1
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyData', 1
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopySchema', 1
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestDatabase', @strDestDB
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestServer', @strToServer
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DropDestObjectsFirst', 1
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestLogin', @strToUser
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestPassword', @strToPassword
	end
else
	begin
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllDefaults', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllObjects', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllrules', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllStoredProcedures', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllTables', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllUserDefinedDatatypes', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllTriggers', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyAllViews', 0

	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopyData', 1
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'CopySchema', 1

	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'IncludeDependencies', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'IncludeGroups', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'IncludeLogins', 0
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'IncludeUsers', 0

	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DropDestObjectsFirst', 1

	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestDatabase', @strDestDB
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestServer', @strToServer
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestLogin', @strToUser
	if @hr=0 exec @hr = sp_OASetProperty @oTransfer, 'DestPassword', @strToPassword
	Select @slice=-1
	--make a list of tables to copy
	WHILE @hr=0 and @slice<>0 and @strTablesTocopy is not null
	       BEGIN
	       SELECT @slice=CHARINDEX(',',@strTablesTocopy)
	       Select @strObjectName=case when @slice=0 then @strTablesTocopy
	       			else LEFT(@strTablesTocopy,@slice-1) end
	       if @slice>0 
			SELECT @strTablesTocopy= STUFF(@strTablesTocopy,1,@slice,'')
	       select @errorMessage='using method AddObjectByName to add table '+@strObjectName
	       exec @hr = sp_OAMethod @oTransfer, 'AddObjectByName',null,
			@strObjectName, 8
	       END
	Select @slice=-1
	--make a list of procedures to copy
	WHILE @hr=0 and @slice<>0 and @strProceduresTocopy is not null
	       BEGIN
	       SELECT @slice=CHARINDEX(',',@strProceduresTocopy)
	       Select @strObjectName=case when @slice=0 then @strProceduresTocopy
	       			else LEFT(@strProceduresTocopy,@slice-1) end
	       if @slice>0 
			SELECT @strProceduresTocopy= STUFF(@strProceduresTocopy,1,@slice,'')
	       select @errorMessage='using method AddObjectByName to add procedure '+@strObjectName
	       exec @hr = sp_OAMethod @oTransfer, 'AddObjectByName',null,
			@strObjectName, 16
	       END
	Select @slice=-1
	--make a list of procedures to copy
	WHILE @hr=0 and @slice<>0 and @strFunctionsTocopy is not null
	       BEGIN
	       SELECT @slice=CHARINDEX(',',@strFunctionsTocopy)
	       Select @strObjectName=case when @slice=0 then @strFunctionsTocopy
	       			else LEFT(@strFunctionsTocopy,@slice-1) end
	       if @slice>0 
			SELECT @strFunctionsTocopy= STUFF(@strFunctionsTocopy,1,@slice,'')
	       select @errorMessage='using method AddObjectByName to add function '+@strObjectName
	       exec @hr = sp_OAMethod @oTransfer, 'AddObjectByName',null,
			@strObjectName, 1
	       END
--exec @hr = sp_OAMethod @objTransfer , 'AddObjectByName' , null , @TableName , 16
	end
select @strCommand = 'Databases("' + @strFromDB + '").ScriptTransfer'
if @hr=0 Select @errorObject=@oserver,
		@errorMessage='using method '+@strCommand
if @hr=0 exec @hr = sp_OAMethod @oServer, @strCommand, @strResult OUT, 
              @oTransfer, 1, @strScriptsDir
    select @strCommand = 'Databases("' + @strFromDB + '").Transfer'
if @hr=0 Select @errorObject=@oserver,
		@errorMessage='using method '+@strCommand
if @hr=0 exec @hr = sp_OAMethod @oServer, @strCommand, NULL, @oTransfer
if @hr<>0
	begin
	Declare 
		@Source varchar(255),
		@Description Varchar(255),
		@Helpfile Varchar(255),
		@HelpID int
	
	EXECUTE sp_OAGetErrorInfo  @errorObject, 
		@source output,@Description output,@Helpfile output,@HelpID output

	Select @ErrorMessage='Error whilst '+@Errormessage+', '+@Description
	raiserror (@ErrorMessage,16,1)
	end
exec sp_OADestroy @oTransfer
exec sp_OADestroy @oServer
return @hr
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO