Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Transfer Database Task and Transfer SQL Server Objects Task in SSIS

MSSQLTips author Arshad Ali By:   |   Read Comments (15)   |   Related Tips: 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


Last Update: 7/19/2010


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, July 19, 2010 - 12:10:17 PM - pkansa Read The Tip

 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?


Sunday, July 25, 2010 - 2:20:16 PM - arshad0384 Read The Tip

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, January 09, 2012 - 3:14:03 PM - André Read The Tip

Hi Arshad Ali,

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

Good tip!!

Tks

 


Tuesday, January 10, 2012 - 5:21:57 AM - Arshad Read The Tip

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


Tuesday, January 10, 2012 - 7:16:09 AM - André Read The Tip

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 ?


Friday, May 11, 2012 - 6:01:52 AM - Elaelian Read The Tip

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


Wednesday, July 11, 2012 - 8:26:30 PM - Rafay Read The Tip

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, July 27, 2012 - 5:03:08 AM - worapot Read The Tip

Good Article

 

I have some Quation.

 

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

how i can?

 

thank for ans.

 


Monday, August 13, 2012 - 2:57:02 AM - Arshad Read The Tip

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.


Tuesday, February 05, 2013 - 12:41:35 AM - vikas Read The Tip

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.   

 

 


Tuesday, April 02, 2013 - 1:01:07 PM - Krishna Read The Tip

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


Saturday, June 01, 2013 - 3:25:21 AM - Nikhil Read The Tip

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


Monday, June 10, 2013 - 5:33:23 AM - Abhishek Read The Tip

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?


Monday, June 10, 2013 - 10:02:06 AM - Arshad Read The Tip

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/


Thursday, May 29, 2014 - 8:27:26 AM - Gagan Read The Tip

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.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.