This article by Jayaram Krishnaswamy shows how to migrate a database from SQL Server 2005 (should work for 2000 as well) to SQL Server 2008 using the Copy Database tool in SQL Server 2008. In an earlier article we saw how this can be done manually by detaching the database from the SQL 2005 and then attaching it to the SQL 2008 server using the SQL Server Management Studio.

Using the Copy Database Wizard you will be creating an SQL Server Integration Services package which will be executed by an SQL Server Agent job. It is therefore necessary to set up the SQL Server Agent to work with a proxy that you need to create which can execute the package. Since the proxy needs a credential to workout outside the SQL 2008 boundary you need to create a Credential and a Principal who has the permissions. Creating a credential has been described elsewhere.

The main steps in migration using this route are:

• Create an Credential
• Create an SQL Server Agent Proxy to work with SSIS Package execution
• Create the job using the Copy Database Wizard

Creating the Proxy

In the SQL Server 2008 Management Studio expand the SQL Server Agent node and then expand the Proxies node. You can create proxies for various actions that you may undertake. In the present case the Copy Database wizard creates an Integration Services package and therefore a proxy is needed for this. Right click the SSIS Package Execution folder as shown in the next figure.

Click on New Proxy.... This opens the New Proxy Account window as shown.

Here Proxy name is the one you provide which will be needed in the Copy Database Wizard. Credential name is the one you created earlier which uses a database login name and password. Description is an optional info to keep track of the proxy. As seen in the previous figure you can create different proxies to deal with different activities. In the present case a proxy will be created for Integration Service Package execution as shown in the next figure. The name CopyPubx has been created as shown. Now click on the ellipsis button along the Credential name and this brings up the Select Credential window as shown.

Now click on the Browse... button. This brings up the Browse for Objects window displaying the credential you created earlier. Place a checkmark as shown and click on the OK button.

The [mysorian] credential is entered into the Select Credential window. Click on the OK button on the Select Credential window. The credential name gets entered into the New Proxy Account's Credential name. The optional description can be anything suitable as shown.

Place a checkmark on the SQL Server Integration Services Package as shown and click on Principals.

Since the present proxy is going to be used by the sysadmin, there is no need to add it specifically.

Click on the OK button to close this New Proxy Account window.

You can now expand the SSIS Package Execution node of the Proxies and verify that CopyPubx has been added. There are two other proxies created in the same way in this folder.

Since the SQL Server Agent is needed for this process to succeed, make sure the SQL Server Agent is running. If it has not started yet, you can start this service from the Control Panel.

Copy Database Wizard

Right click the Management folder to open a contextual menu as shown.

Click on Copy Database... menu item.

This opens up the Copy Database Wizard's Welcome screen. Read the info on this screen which says that you can migrate database on your SQL 2000 and SQL 2005 servers with this tool.

Click on the Next button.

This brings up the screen where you need to choose a source of your database. This source can either be SQL 2000 server or SQL 2005 server. However (oops!) the default that comes up is SQL 2008 server as shown. Click on the Browse... button which brings up the Browse for Servers window where you expand the Database Engine and highlight the SQL Express server (Junior version of SQL 2005 Server) as shown. Note that the figure is a collage of two windows and couple of clicks.

Click on the Next button. This brings up the window where you choose a destination server showing SQLExpress as the default server (oops again, they seem to have it backwards). Use the ellipsis button to add the Hodentek2HTEK2008 server to the choose the destination server window as shown. Again as this is set up with windows authentication the default is accepted.

Click on the Next button. This takes you to the Select Transfer Method window of the Copy Database Wizard where you can use either the attach / detach method, or the method that uses the SQL DMO API by making the right choice. The second method which does not stop the server is chosen here although it may be a little slower than the other method.

Click on the Next button. This opens up the Select Databases section of the copy database wizard. Here you can pick and choose what databases will be used in this migration. Here the database pubsx on the SQL Express is chosen to be copied. Note that you can also move the database. It is good practice to refresh before you move away from this screen.

Click on the Next button. This brings up the Configure Destination Database (1 of 1) window. If a database with the same name exists on the destination, you can either stop the transfer, or you can drop the existing one before transfer by choosing the appropriate radio button at the bottom of the screen as shown. The folder locations where the transferred database files will be saved to are shown here . If you want you can choose another name for this database. Here the default is accepted.

Click on the Next button. In the screen that shows up, you can pick and choose database objects, an option that is not available in manual attach / detach procedure. Similarly you can choose what logins to migrate by clicking on the ellipsis button in this window (the right hand area). You can use the >> and << buttons to add, or remove objects that needs transferring. Here the default is accepted.

Click on the Next button. This is where an "Integration Services Package" is created whose properties will be configured here. The execution logging options can be chosen here. This can be Windows events log or, a text file that can be chosen as shown.

Click on the Next button. This brings up the window for the Schedule the Package step of the wizard which needs to be configured. It can be, Run immediately or, it can be scheduled using a screen that would pop-up if that option is chosen. Here the "Run immediately" option is chosen. This is the screen where you would use the proxy account for the SQL Server Agent you created earlier. Click on the drop-down handle and choose the proxy you created earlier.

After choosing the proxy indicated, click on the Next button. This takes you to the summary of actions taken so far in the Complete the Wizard screen as shown.

Click on the Finish button in the Complete the Wizard window. This brings up the Performing Operations section of the wizard and shows the processing of the various steps and finally displays the success of the operation. In case there is an error it will stop processing and display a hyperlink, which when clicked will show the error in more detail.

Click the Close button to close the window. You may now refresh the databases node in the SQL Server Management Studio server and verify that the database has been migrated. The two figures show the before and after migration contents of the databases folder in the SQL Server Management Studio.

Before Transfer

After Transfer

In the SQL Server Agent folder you can see the job created for this migration as shown. There are two other jobs created earlier.

If you bring up the properties of this page by right clicking the job you can also see the job history.

Summary

Copying a database from an SQL 2005 Server to an instance of SQL Server 2008 is described using the Copy Database tool available in the SQL Server 208. Procedure to create a proxy to work with this task is also described. The transfer method adopted in this article allows you to transfer chosen objects without stopping the source server. The transfer is painless as long as the security is not violated. Make sure you record the execution logging of the transfer as this is invaluable in the post mortem of failed transfers.

About the Author