Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Upgrading to SQL Server 2008 using Copy Database Wizard


By:   |   Read Comments (5)   |   Related Tips: More > Upgrades and Migrations

Attend these FREE MSSQLTips webcasts >> click to register


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, March 18, 2010 - 12:44:17 PM - Erick Back To Top

Hi Ashish, the user I'm using has sysadmin privileges. The message sent by the wizard is "The destination must be a SQL Server 2005 database". The source is a SQL 2005 standar and the destination is a SQL Server 2008 Enterprise


Thursday, March 18, 2010 - 12:00:36 AM - Ashish Kumar Mehta Back To Top

Hi Erick,

When you are using Copy Database Wizard, you should be having sysadmin privelages on the destination server. I hope you have the necessary permission. Also check the following link http://support.microsoft.com/kb/940375/en-US

HTH,

Ashish


Wednesday, March 17, 2010 - 6:50:19 PM - Erick Back To Top

Hello, I was reading this article and found it very interesting, but didn't mention, that it could be done only with developer or enterprise edition. I have the standar and it doesn't allow it. Any ideas to copy databases using the standar edition?


Wednesday, January 20, 2010 - 9:04:16 AM - admin Back To Top

If you have two machines one for SQL 2000 and one for SQL 2005 the better approach would be to use a backup and restore to do the upgrade.

Also, you will need to migrate your logins from SQL 2000 to SQL 2005.  This can be done using this script: http://support.microsoft.com/kb/246133

Also, you will need to create your SQL Agent jobs on the new server as well.


Wednesday, January 20, 2010 - 6:10:46 AM - yakaaithiri Back To Top

hi Admin ,

 whether this copy databse wizard can be used to transfer Database from SQL2000 to SQL 2005.

What r all the basic things i need to copy.

I am in middle of a migration , we r running SQL 2000 now and planning to upgrade to 2005.

If we use copy database wizard to copy to the new SQL2005 instance , will everthing work fine .

Will the users get connected to the nes servers just as like SQL 2000.

Please guide me in this .

Regards,

Balabharathi Narayanan


Learn more about SQL Server tools