Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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 | February 2008 | .NET Microsoft

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. 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 by Jayaram Krishnaswamy, 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.

(For more resources on Microsoft, see here.)

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.

Interested readers may also want to read my other popular article Moving Data from SQL Server 2000 to SQL Server 2005

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).

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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.

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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.

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.)

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.

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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.

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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.

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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.

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:

(For more resources on Microsoft, see here.)

Some Transferred Objects

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

Tables

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

Stored Procedures

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

Users

Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps

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.


Further resources on this subject:


About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

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


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