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.

(For more resources on Microsoft, see here.)

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.