SQL 2005: SQL Server Integration Services
Transfer Database Task
By: Zack Turnbow
Introduction
Moving forward on the quick tour of SSIS, the next few items that are going to be covered are database maintenance tasks followed by deploying a package and finally calling a SSIS package from some C# code. The most common of the database maintenance tasks is the Transfer Database Task which does exactly what it says.
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
Scenario
Moving or copying a database can be a mundane repetitive task. Some of the common reasons for transferring a database are: deploying a database for the first time, copying live data to a test or development region, or even moving a database to a new server due to new hardware deployment. Whatever the reason may be, the Transfer Database Task can make the job easier, especially if it is a task that happens on a regular basis. This article will deal with transferring live data to a new SQL server that doesn’t have the database yet.
Implementation
Since this is a maintenance task, a new blank project is needed. There is no need to copy and paste the previous SSIS package that was used in the last article. So, with a clean slate, click on the Control Flow tab. All of the work will be done in the Control Flow tab and only one component is needed. Locate and drag the Transfer Database Task from the toolbox to the work area.
[Click to enlarge]
Double click the component to bring up the editor window. As with most components there are several different ways to configure the Transfer Database Task. Click on the tab to get to the main configuration information.
[Click to enlarge]
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
First, click in the box next to the SourceConnection and an option to add a new connection is available. Keep in mind that the task is to move or copy a database and not to work with any specific table data so if there are any other connections listed that pertain to a table then it is best not to use that connection. When the adding a new connection, another configuration window will appear and will need to be filled out. This information is where the database is physically located so make sure the fully qualified path and network share path are available.
[Click to enlarge]
Once the proper information has been filled in, click OK and then repeat the process DestinationConnection.
[Click to enlarge]
In the next table for the Destination table information, fill in the name of the database and whether there is a need to overwrite the existing destination database. In this example, there is no destination database but the option to overwrite is set to True. In the Source table, the option to copy or move the database is available. Since the destination is a new SQL server instance, the copy option is used. Next there is an option to move the database while it is online (in use) or offline (not in use). Since the database being transferred is small at this point and there is a requirement that the database must be available at all times, the DatabaseOnline option is selected. For performance purposes, if the database being transferred is a large database then the DatabaseOffline would be a better choice. Finally, fill in the name of the database that is being transferred.
Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!
[Click to enlarge]
Once this is completed, click OK.
Testing the Package
There are no other components to configure. Everything is contained in this one component so it is time to test the package. Run the package but please note that the package may take a little longer to run than the previous packages that have been covered in previous articles.
[Click to enlarge]
Once the package has finished running, let’s double check to make sure the database was transferred properly. Open up SQL Management Studio and connect to the destination database server. Expand the list of databases to verify that the CreditCard database is there. You can go further and open up the Transaction table to compare the data from the source database.
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
[Click to enlarge]
What have we learned?
How to configure a Transfer Database Task.
The different uses for transferring a database.
What the different transfer options and methods are available (Move vs. Copy and Online vs. Offline).
Attachments
Leave a Comment
#1 Joar Fagerli
Posted By: Joar Fagerli | 11.19.2008 at 5:41 AM
I have never been able to do that transfer database task (frustrated), but I would be very happy if I could! With SQL server 2000 I could do a "transfer all objects". My source db is on a web-server to which I have not all rights of cource (I may connect to it). Should I still be able to do the transfer database task? From that server to my local PC?
#2 plus grands bonus des casinos virtuels
Posted By: plus grands bonus des casinos virtuels | 2.13.2010 at 4:27 AM
Database Administrators can use the Transfer Database Task to transfer a SQL Server database between two instances of SQL Server. Using database online mode the database will remain attached on the source SQL Server and the entire object within the database will be transferred to the destination SQL Server using SQL Management Object (SMO).
#3 games addicting
Posted By: games addicting | 3.07.2010 at 3:37 AM
hi blogger, I found your blog from yahoo and read a few of your other articles. They are nice. Pls continue this great work!! Have a nice day, Olivia.
#4 220-701
Posted By: 220-701 | 3.19.2010 at 5:34 AM
For all I know this could be a really old entry, since there are no date marker on the article. In that case I apologize.
#5 testking 70-291
Posted By: testking 70-291 | 5.12.2010 at 1:57 AM
I like your blog very much.
#6 testking 70-432
Posted By: testking 70-432 | 5.12.2010 at 1:58 AM
Your blog is great.
#7 testking 70-450
Posted By: testking 70-450 | 5.12.2010 at 1:58 AM
Thanks for sharing.
#8 air max shoes
Posted By: air max shoes | 5.23.2010 at 4:58 AM
nike air max sale
#9 wholesale shoes
Posted By: wholesale shoes | 5.24.2010 at 3:25 AM
Spring summer pocket perfect supporting role (figure) except heart shape brooch, earring, necklace,
#10 military gear
Posted By: military gear | 6.01.2010 at 5:55 AM
I am glad that people are stepping up and doing something about it.
#11 coach shoes
Posted By: coach shoes | 6.11.2010 at 4:21 AM
Thanks for your sharing ROY, I appreciate this. keep up the good work
#12 michael jordan shoes
Posted By: michael jordan shoes | 6.18.2010 at 8:56 PM
good post!!thank you
#13 Bottle Openers
Posted By: Bottle Openers | 6.22.2010 at 3:04 AM
Going through your site gives me a chance to recall why I enjoy reading things with so much thoughts. It is nice to know that there are still great bloggers out there that can put fun into knowledgable information. Thank you for your contributions and eagerness to share your ideas with us.
#14 juicy couture
Posted By: juicy couture | 7.06.2010 at 11:35 PM
It clarifies a lot of things.
#15 gucci bag
Posted By: gucci bag | 7.18.2010 at 11:09 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#16 fendi bags
Posted By: fendi bags | 7.18.2010 at 11:10 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#17 coach handbags on sale
Posted By: coach handbags on sale | 7.18.2010 at 11:10 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#18 coach handbags on sale
Posted By: coach handbags on sale | 7.18.2010 at 11:10 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#19 coach bag
Posted By: coach bag | 7.18.2010 at 11:10 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#20 coach purse
Posted By: coach purse | 7.18.2010 at 11:10 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#21 hermes birkin
Posted By: hermes birkin | 7.18.2010 at 11:10 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#22 chanel purses
Posted By: chanel purses | 7.18.2010 at 11:11 PM
I am really interested in this program but I do not know much about it. After reading your article, i have more experiences to work with it. Your post is interesting and picturesque. I hope i can get your post in next time. Keep on!
#23 tiffany co
Posted By: tiffany co | 7.21.2010 at 2:26 AM
For classic and quality genuine silver jewelry, many people choose Tiffany & Co brand.Now,we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#24 tiffany jewelry
Posted By: tiffany jewelry | 7.21.2010 at 2:26 AM
For classic and quality genuine silver jewelry, many people choose Tiffany & Co brand.Now,we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#25 tiffany jewellery
Posted By: tiffany jewellery | 7.21.2010 at 2:26 AM
For classic and quality genuine silver jewelry, many people choose Tiffany & Co brand.Now,we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#26 tiffany ring
Posted By: tiffany ring | 7.26.2010 at 6:50 AM
Tiffany On Sale Offer a wide range of fashionable Tiffany & Co silver jewelry, including Necklaces, Pendants, Bracelets, Earrings, Rings and Accessories with fantastic discount.more http://www.tiffanyonsale.com/
#27 tiffany bracelet
Posted By: tiffany bracelet | 7.26.2010 at 6:50 AM
Tiffany On Sale Offer a wide range of fashionable Tiffany & Co silver jewelry, including Necklaces, Pendants, Bracelets, Earrings, Rings and Accessories with fantastic discount.more http://www.tiffanyonsale.com/
#28 tiffany necklace
Posted By: tiffany necklace | 7.26.2010 at 6:50 AM
Tiffany On Sale Offer a wide range of fashionable Tiffany & Co silver jewelry, including Necklaces, Pendants, Bracelets, Earrings, Rings and Accessories with fantastic discount.more http://www.tiffanyonsale.com/