Transfer Database Task and Transfer SQL Server Objects Task in SSIS

By:   |   Comments (18)   |   Related: More > Integration Services Control Flow Transformations


Problem

Making copies of databases, moving databases to another SQL Server instance and transferring SQL Server objects to another database are frequent tasks a DBA performs. In this tip I am going to demonstrate how you can use SSIS to perform these operations.

Solution

In my previous tips, I showed how you can use SMO to programmatically manage a SQL Server instance. In the Transfer database schema objects and data to another server database tip, I showed how you can transfer SQL Server objects programmatically between databases or SQL Server instances. Now I am going to demonstrate you how you can perform these operations by using the Transfer Database Task and Transfer SQL Server Objects Task in SSIS without having to write any code.

Transfer Database Task

The Transfer Database Task is used to move a database to another SQL Server instance or create a copy on the same instance (with different database name). This task works in two modes as shown below:

  • Offline : In this mode, the source database is detached from the source server after putting it in single user mode, copies of the mdf, ndf and ldf files are moved to specified network location. On the destination server the copies are taken from the network location to the destination server and then finally both databases are attached on the source and destination servers. This mode is faster, but a disadvantage with mode is that the source database will not available during copy and move operation. Also, the person executing the package with this mode must be sysadmin on both source and destination instances.
  • Online : In this mode, the task uses SMO to transfer the database objects to the destination server. In this mode, the database is online during the copy and move operation, but it will take longer as it has to copy each object from the database individually. Someone executing the package with this mode must be either sysadmin or database owner of the specified databases.

Now let me demonstrate how you can create an SSIS package with the Transfer Database Task.

Go to START -> Microsoft SQL Server 2005/2008 -> SQL Server Business Intelligence Development Studio to launch BIDS.

Then go to File menu -> New -> Project -> Select "Business Intelligence Projects" in the left tree pane -> Select "Integration Services Projects" and name the project as you wish and click OK.

In this new project you will see one package is already added with the name "Package.dtsx". Drag the "Transfer Database Task" from the Toolbox (which is normally on the left side) to the Control Flow pane. Right click on the task and select Edit... as shown below.

demonstrate how you can create an SSIS package with the Transfer Database Task.

In the "Transfer Database Task Editor", select Databases on the left and now you are ready to configure this task. SourceConnection is the property to specify the connection for the source SQL Server instance, if you have already created a connection manager then you can reuse it here or can create a new one as shown below. This will also need to be done to configure the DestinationConnection property as well.

In the "Transfer Database Task Editor", select Databases on the left

Next you need to specify the values.

  • Connections
    • SourceConnection - the source instance
    • DestinationConnection - the destination instance
  • Destination Database
    • DestinationDatabaseName - name of the new database
    • DestinationDatabaseFiles - name and location of the database files
    • DestinationOverwrite - if the database already exists on the destination server it will give you an error, so if you want to overwrite the destination database you can set this property to True.
  • Source Database
    • Action - whether you want to copy or move a database.
    • Method - whether you want the copy and move operation to be offline (in this case you also need to provide the network share name which will be used to transmit the database files.
    • SourceDatabaseName - name of the source database
    • SourceDatabaseFiles - name and location of the database files
    • ReattachSourceDatabase - is another property which you can set to TRUE to reattach the source database after the copy operation.

Once you are done with all these configurations you can hit F5 or click on the play icon to execute the package. Your task will turn yellow during the execution and then either red or green depending on the execution outcome. You can go to progress tab to see error messages if the execution fails. Although failure is rare, it is possible if your source database is smaller than the size of the model database on the destination server.

hit F5 or click on the play icon to execute the package

You can also use Copy Database Wizard to copy and move your database, for details refer to Upgrading to SQL Server 2008 using Copy Database Wizard.

Transfer SQL Server Objects Task

The Transfer SQL Sever Objects task is used to transfer one or more SQL Server objects to a different database, either on the same or another SQL Server instance. This allows you to select different types of objects you want to transfer. You can select tables, views, stored procedures, user defined functions etc. Not only this, you can select a combination of these types of objects to transfer and even select particular objects of a particular object type.

Create another package in the same project and drag a "Transfer SQL Server Objects Task" to the Control Flow. Right click on the task and then select Edit. In the "Transfer SQL Server Objects Task Editor" click on Objects to set the different properties for this task as shown below.

Transfer SQL Server Objects Task

Similar to the way you configured the SourceConnection and DestinationConnection for the Transfer Database Task, you need to configure these properties for this task as well. SourceDatabase is the name of the database from where you are copying the objects and DestinationDatabase is the name of the database to which you are copying the objects to.

  • Connection
    • SourceConnection - the source instance
    • SourceDatabase - name of the source database
    • DestinationConnection - the destination instance
    • DestinationDatabase - name of the new database
  • Destination
    • DropObjectsFirst - Drop selected objects on the target before copy
    • IncludeExtendedProperties - While copying operation also include extended properties of SQL objects being copied
    • CopyData - While copying tables, transfer the data of the selected tables as well
    • ExistingData - whether to append or replace data
    • CopySchema - Copy the schema of the objects being copied
    • UseCollation - Make sure collation of the columns are appropriately set on copied tables
    • IncludeDependentObjects - Include all the objects in copy operation which are dependent on selected objects
  • Destination Copy Objects
    • CopyAllObjects - Do you want to copy all objects from the source database, if set to False, next property ObjectsToCopy will get enabled.
    • ObjectsToCopy - With this property you select types of objects you want to copy. You can select all objects of one or more types or select particular objects as you can see in the below image. Depending on the SQL Server version, type of objects selection will vary.
  • Security
    • CopyDatabaseUsers - whether to include users
    • CopyDatabaseRoles - whether to include roles
    • CopySQLServerLogins - whether to include logins
    • CopyObjectLevelPermissions - whether to include object level permissions
  • Table Options
    • CopyIndexes - whether to include indexes
    • CopyTriggers - whether to include triggers
    • CopyFullTextIndexes - whether to include full text indexes
    • CopyAllDRIObjects - whether to include referential integrity objects
    • CopyPrimaryKeys - whether to include primary keys
    • CopyForeignKeys - whether to include foreign keys
    • GenerateScriptsInUnicode - whether to create script in Unicode or not

Below is a partial listing of the objects that can be selected.

Depending on the SQL Server version, type of objects selection will vary

The person executing the package with this task must have at least browse objects permissions on the source database and on the destination database must have permissions to drop and create objects.

Hopefully these are additional tools you can use next time you need to transfer objects or databases in your environment.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, April 6, 2018 - 10:54:54 AM - shah Back To Top (75627)

 

 ASAK Arshad bhai,

 

 

I have 2 requirements can you please help me related to automation.

 

First: - Backup the database from prod to restore in preprod .. with different file locations and same file locations.

 

I get this refresh routine request which I want to automate for 8 applications with 8 different databases. I want a solution which should do in one click with a sql job, we take netbackups but I want this with a copy only backup option ( like with ssis package with some environmental variable or with expressions etc.. or even with a sproc.

 

I am doing separately for all apps, like backup copy only on local disk using ssis or robocopy to transfer file to destination server, then restore with a script but that still can’t be single job, one job in source to backup and copy to destination and then in destination server restore …

 

But this thing can I achieve with ssis to identify the servername or application database backup and restore … I didn’t find anything on google.

 

Second: - while restoring the production sensitive data need to be anonymised/mask.

 

Please let me know is there any way work around, I am a newbie SQL don’t know much details and with all honesty don’t have scripting skills.

 

Wednesday, May 11, 2016 - 2:46:16 AM - Saketh Back To Top (41452)

Is it possible to transfer big databases from one server to other server.

Is it possible to do it remote location with this method which is on cloud? 

How much time it takes to copy a 200 GB of production database to another server without downtime using this method.

 

 

Tuesday, September 16, 2014 - 1:57:58 AM - Munish Bansal Back To Top (34539)

Can we copy data from a table to another EXISTING table residing into another database?

Thursday, May 29, 2014 - 8:27:26 AM - Gagan Back To Top (31986)

I need a help in C#----------I have a string that contain a Line of Statement ,I want  to add some character in string after(in case of Counting Three Space ), means first find space in a string ,no add charcter, find next Space ,no add character, But finally geeting Third Space in sting add () this one after the string , and lastly whole string will remain same.

Monday, June 10, 2013 - 10:02:06 AM - Arshad Back To Top (25374)

Hi Abhishek,

 

You can try using SSIS , SSIS have several inbuilt tasks for carrying out these kind of works.

http://www.mssqltips.com/sqlservertutorial/200/sql-server-integration-services-ssis/

Monday, June 10, 2013 - 5:33:23 AM - Abhishek Back To Top (25366)

Hi Arshad,

Is there any way to automate the process of copying tables from one database to another that are present in different databases?
I've tried the below but it results in error that it doesn't recognize the database-

                 Select * into [server2].[db2].[table2] from [server1].[db1].[table1]

I need to automate this process using Perl.
Please let me know if there exists a way  by which we can eliminate constraints issue and do this automation using a query?

Saturday, June 1, 2013 - 3:25:21 AM - Nikhil Back To Top (25230)

Hi Sir,

               While using Transfer Database Task to copy "AdventureWorks"  to "STG" DB,We have faced one issue..

Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=0 description=Foreign key 'FK_StoreContact_Store_CustomerID' references invalid table 'Store'. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}). The Execute method must succeed, and indicate the result using an "out" parameter.

But Store Table is exist in AdventureWorks.................

We have drop FK_StoreContact_Store_CustomerID then we have run Transfer Database Task.
 
Then 
 
Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=0 description=Foreign key 'FK_StoreContact_Contact_ContactID' references invalid table 'Contact'. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}). The Execute method must succeed, and indicate the result using an "out" parameter.
 
But Contact Table is exist in AdventureWorks.................

Please provide solution for this.
Thanks in advance

Tuesday, April 2, 2013 - 1:01:07 PM - Krishna Back To Top (23118)

Hi Arshad Ali,

 

Thanks for your post. Could you please recommand what are the basic things that i need to initiate SSIS pacakge( like Versions , permissions in source and destinations)? Here is my requriement:

  Create SSIS package to move all databases and logins from one server to another server.  This package would move database and logins from source server which has SQL 2000 and SQL 2005 to Destination server which has SQL 2008R2.


Thanks,

Krishna


Tuesday, February 5, 2013 - 12:41:35 AM - vikas Back To Top (21913)

Thank you for posting this helpful article !

I have many tables with huge data, I have to transfer this database to another server. I dont want to distrub diffrential/log backup sequence of source database. As we have "Copy only backup"  option in sql server , Is there any way to do this through SSIS.

If I select "Transfer sql server object task" It may take long time.   

 

 

Monday, August 13, 2012 - 2:57:02 AM - Arshad Back To Top (19013)

Well in that case you should consider enabling Change Data Capture at your source table and pull only incremental data; you can find more details about it here:

http://www.mssqltips.com/sqlservertip/1474/using-change-data-capture-cdc-in-sql-server-2008/

The above solution is applicable if your source is SQL Server 2008 and later but if your source has an older version you need to use your own custom logic, for example pull only those records which got added or updated in source since the last data pull by doing comparison on CreatedDate or ModifiedDate columns of the source table.

Friday, July 27, 2012 - 5:03:08 AM - worapot Back To Top (18823)

Good Article

 

I have some Quation.

 

if i want transfer data on update record not transfer existing record 

how i can?

 

thank for ans.

 

Wednesday, July 11, 2012 - 8:26:30 PM - Rafay Back To Top (18454)

Hi quite useful article but i am not able to do any update in it.. i have to drop the schema first then have to update it.. of if i change the column name it gives error that datasource object not found can you guide me into this..??

Friday, May 11, 2012 - 6:01:52 AM - Elaelian Back To Top (17416)

Hi,

First, thank you for this post!

I have a lot of tables to transfer from one database to another (about 300). My aim is to improve performance and reduce duration. Do you think that it could be better to transfer the tables in different tasks than in one?

For instance, I was thinking of use one task to transfer the first hundred tables, then another task to transfer the 100 others...

What is your opinion about that?

Thank you in advance,

Elaelian

Tuesday, January 10, 2012 - 7:16:09 AM - André Back To Top (15589)

Thanks,

Hum,businnes intelligence development studio not supported in SQL 2008 Workgroups

:( very sad!!

 

I just need to create and scheduler a transfer database task.
Its will run once a day. 

Any idea ?

Tuesday, January 10, 2012 - 5:21:57 AM - Arshad Back To Top (15583)

I don't think so other than basic Import and Export wizard, you can get more details about the features supported in each edition of SQL Server 2008 here:

http://msdn.microsoft.com/en-us/library/cc645993(SQL.100).aspx

Monday, January 9, 2012 - 3:14:03 PM - André Back To Top (15569)

Hi Arshad Ali,

This service (SSIS) is avaliable in SQL Server 2008 WorkGroups?

Good tip!!

Tks

 

Sunday, July 25, 2010 - 2:20:16 PM - arshad0384 Back To Top (5889)

Seems like permission issue, check this:

The person executing the package with this task must have at least browse objects permissions on the source database and on the destination database must have permissions to drop and create objects.

Thanks,

Arshad

Monday, July 19, 2010 - 12:10:17 PM - pkansa Back To Top (5862)

 When I'm running this, I'm hitting this error (please note I've replaced the actual user and login names):

Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [username] FOR LOGIN [loginname] WITH DEFAULT_SCHEMA=[dbo] " failed with the following error: "'username' is not a valid login or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}). The Execute method must succeed, and indicate the result using an "out" parameter. 

 

Any thoughts?















get free sql tips
agree to terms