Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

Exclusive offer: get 50% off this eBook here
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 — Save 50%

An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.

$23.99    $12.00
by Jayaram Krishnaswamy | April 2008 | .NET Microsoft

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

 

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

(For more resources on Microsoft, see here.)

Copy Database Wizard

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

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

After Transfer

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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

 

Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard

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.


Further resources on this subject:


Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :



Books From Packt


Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Least Privilege Security for Windows 7, Vista and XP
Least Privilege Security for Windows 7, Vista and XP

Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform


Your rating: None Average: 4 (3 votes)
Version90 database compatibility level is not supported. by
When I try to copy a database from a 2005 DB to 2008, I get this error: "Version90 database compatibility level is not supported."
Fails due to compatiblity level error by
Version90 database compatibility level is not supported.
Migration DATABASES by
Good afternoon, This is my first post in the forum, and I'm already grateful for your help! ____________________ The intention is to migrate the databases (including reporting services) applications System Center Operations Manager and System Center Configuration Manager for SQL Server 2008 centralized. At a time when each of the servers there is a dedicated sql server 2005. ____________________ I would appreciate your help (with links, pdf's, whatever). If they need any more information, just ask (although novice in this area). Thank you. (My E-mail: q.uicks.ilver@hotmail.com) (I'm sorry aboiut my English)
Great advice by
This advice saved my life this afternoon - thanks!

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
C
g
z
n
Q
q
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software