Introduction

There are several options if one wishes to move a database from a SQL Server 2005 to SQL 2008 Server. First of all there is a 'Copy Database Wizard' in SQL 2008 Server which is meant for transferring a database from any version of SQL Server 2000 and above to 2008 version. This Wizard can operate in two ways. In the first option it can attach a database (even one on the network) and uses the SQL 2008 SQL Server agent. The Copying of the database is implemented by an Integration Services package to run as a SQL Server Agent job that is scheduled to run immediately or according to some configurable schedule. This will therefore depend on correctly configuring the SQL Server Agent. In order to use the attach / detach process, the remote server will be stopped and if the database / log files are on a shared drive they are correctly brought in by the wizard. In the other option the database will be copied using the SQL Server Management Program for which the source database need not be stopped. However this is slower than the previous method and would also require the SQL Server Agent since a package has to be run.

An option which works without too much hassles is manually detaching and attaching the database/log files. In this step-by-step (really two steps) tutorial, this simple procedure is described. If you are just interested in taking a small database from 2005 to 2008 server the author strongly recommends this procedure.

Step 1: Detaching the Database

Highlight the database you want to transfer in the Databases node in the SQL Server Management Studio as shown in the next figure. Right click this database as shown and click on Detach... Make sure the database is running (notice the green arrow for HodentekSQL Express which is a junior version of SQL 2005).

This brings up the Detach Database window as shown. Place a check mark for 'Drop' as shown and click on OK. This removes the 'Pubs' node from the Databases folder in the SQL Server Management Studio (You may need to attach it again). With this accomplished you can physically move the files or do what you want with them.

Step 2: Copy the DATA / LOG Files

Copy the pubs.mdf and pubs.ldf files to a location on the C: drive of the machine on which SQL 2008 Server is installed.

Step 3: Attaching the Database

In the SQL Server Management Studio, right click the databases node and choose Attach... pick list item as shown in the following figure.

This pops-up the Attach Databases window as shown in the next figure.

Click on the Add button. This brings up a Locate Database Files folder / file browser window. Locate the files you placed in Step 2. In this tutorial example, the files were placed in the location shown in the next figure.

Click open the folder which contains the DATA /LOG files and highlight the MDF file. Now click on the OK button. This brings in the file information into the Attach Databases window. Although you just chose the MDF file, the LDF file is automatically brought in as well.

Now click on the OK button in the Attach Databases window. Within a few seconds the pubs database appears in the databases folder of the SQL 2008 server. Expand the pubs node and verify the objects as shown in the next figure.

Some Transferred Objects

Some of the objects copied over are shown in the next paragraphs. The figures show only portions of their contents.

Tables

Stored Procedures

Users

Summary

The simple procedure outlined in this tutorial is adequate if you need to transfer databases from MS SQL Server 2005 to MS SQL Server 2008. If you need to selectively transfer logins, or choose objects to transfer perhaps the Copy Database Wizard may be better suited. In order to use the wizard you have to understand the security features well and be able to create an appropriate SQL Server Agent proxy. This will be described in detail in a future article at this site.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.