By: Ashish Kumar Mehta | Comments (5) | Related: > Upgrades and Migrations
Problem
As like in any organization we are also planning the migration of databases from SQL Server 2005 to SQL Server 2008. As there is only one database which needs to be migrated as of now, I think it would be ideal to use the Copy Database Wizard as it is best suited for such a scenario. Can you give us a detailed explanation of how to use the Copy Database Wizard to upgrade a database?
Solution
The Copy Database Wizard is an excellent option which is available to database administrators who need to upgrade a small or medium sized database from SQL Server 2005 to SQL Server 2008.
Using the Copy Database Wizard, database administrators can not only copy the database, but they can also copy additional objects which are available outside the user database such as Stored Procedures available in the master database, SQL Server Agent Jobs, User-defined error messages, Endpoints, SSIS Packages, Logins etc. This wizard basically creates one or more SQL Server Integration Services (SSIS) packages to perform the migration.
The migration steps could be to use SQL Server Management Objects (SMO) or to detach the database from SQL Server 2005 and attach the database to SQL Server 2008. In this tip I will show the steps which you need to follow in order to migrate a database from a SQL Server 2005 server to a SQL Server 2008 server.
Migrating Database Using Copy Database Wizard
Following are the steps you need to take to migrate a database from SQL 2005 to SQL 2008.
1. Connect to the SQL Server 2005 instance using SQL Server Management Studio. Then right click the Products database which we will migrate for this example. From the popup menu, select "Tasks" and then "Copy Database..." option as shown in the snippet below. This will open up the Copy Database Wizard.
2. In the "Welcome to the Copy Database Wizard" screen click Next to continue with the wizard.
3. In "Select a Source Server" wizard screen; select the SQL Server 2005 source server name and choose the appropriate authentication mode and click Next to continue with the wizard.
4. In the "Select a Destination Server" wizard screen; select the SQL Server 2008 destination server name and choose the appropriate authentication mode and click Next to continue with the wizard.
5. In the "Select the Transfer Method" wizard screen; you can choose one of the available transfer methods namely "Detach and Attach Method" or "SQL Management Objects Method" to migrate the database. In this tip, we will be using the Detach and Attach Method which will require the source database to go offline. This is the preferred method to upgrade or move very large databases. However, no user connections are allowed on the source database when you are using this option. It is always a good practice to select the checkbox "If a failure occurs, reattach the source database" as this will help to get the source database back online in case of a failure. Click Next to continue with the wizard.
6. In the "Select Databases" wizard screen; you need to select the database which you want to migrate from SQL Server 2005 to SQL Server 2008. The important thing which needs to be noted is that there is an option available to Move as well as to Copy the database. However, it's a good practice to use the Copy option as this will retain a copy of the database on the SQL Server 2005 server after the migration to SQL Server 2008. In this tip, I have selected the Copy option for the Products database for the migration. Click Next to continue with the wizard.
7. In the "Configure Destination Database (1 of 1)" wizard screen; specify database file names and also specify the options whether to overwrite existing databases at the destination and click Next to continue with the wizard.
8. In Select Server Objects wizard screen; select additional objects outside the databases that you are interested in copying to SQL Server 2008 server. This is where you have the option of moving stored procedures from the master database, SQL Server Agent jobs, user defined error messages, endpoints and even SSIS packages. Click Next to continue with the wizard
9. In the "Configure the Package" wizard screen; you can name the SSIS package and also specify the logging option which could be either writing to a Windows event log or writing to a Text File. It would be good to create a log just so you can see what occurred and also if there are any problems. Click Next to continue with the wizard.
10. In the "Schedule the Package" wizard screen; you can choose either to run the SSIS package immediately or to run the SSIS package at a scheduled time. In this screen you can also specify the Integration Service Proxy account, this specifies the user account that will be used to run the package. Click Next to continue with the wizard.
11. In the "Complete the Wizard" screen; you can verify the choices which you have made so far within the Copy Database Wizard and click the Finish button to begin the database migration.
12. Once the migration is successful you will be able to see the below wizard screen.
Next Steps
- That is the entire process for migrating a database from SQL 2005 to SQL 2008 using the copy database wizard. If you have a small or medium sized database and want to do a quick transfer this may be the simplest method.
- Remember that in addition to moving the database you can also move stored procedures from the master database, SQL Server Agent jobs, user defined error messages, endpoints and SSIS packages
- Here are some additional tips related to SQL 2008
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
