How to Transfer the Microsoft SQL Database
Microsoft SQL Server 2008 supports transferring databases using SQL Server Integration Services. The task allows administrators to copy or move a database from one database instance to another on the same or a remote server. Database transfers can be done in online mode without stopping users from accessing the data or offline mode when the database is down. You can set up a database transfer in SSIS using the Transfer Database Task.
Other People Are Reading
Instructions
-
-
1
Open SQL Server Integration Services (SSIS) designer and create a new package or select an existing one to use.
-
2
Locate the "Transfer Data Task" in the toolbar on the left and drag it to the right side of the screen.
-
-
3
Type a name for the database task and a description, if desired, in the General properties window.
-
4
Switch to the Databases page of the task editor box.
-
5
Enter the source database and destination database information, creating new connections to the servers if needed.
-
6
Enter the destination database information, including the desired name, file names and file location. If the destination database already exists, select overwrite options.
-
7
Select the desired action and method. The action is either copy or move. The method is either online mode or offline mode.
-
8
Enter the source database information, including the name and file names.
-
9
Choose what you want done in case of failure. You can either set the task to reattach the source database or leave it offline in the event of a failure.
-
10
Apply the changes and click "OK."
-
11
Execute the package to transfer the database.
-
1
Tips & Warnings
Sysadmin rights are required to perform offline transfers. Online transfers require membership in either the sysadmin or DBO roles.
References
Resources
- Photo Credit database on paper image by .shock from Fotolia.com
