Upgrading to SQL Server 2008 Using Detach and Attach Operations

By: Ashish Kumar Mehta | Comments (3) | Related: > Upgrades and Migrations

Problem

Like most other organization, we are planning the migration of a database from SQL Server 2005 to SQL Server 2008. As there is only one database which needs to be migrated as of now, I think it would be ideal to use the detach and attach operation to upgrade the user database from SQL Server 2005 to SQL Server 2008. In this tip we will go through a detailed explanation of how to use the detach and attach operation to upgrade a database.

Solution

The detach and attach operation is an excellent method available to database administrators who need to upgrade a small or medium sized database from SQL Server 2000 or SQL Server 2005 to SQL Server 2008.

Moving a database by using the detach and attach operations involves the following tasks:

1. Detaching the user database.
2. Copying the database (.MDF, .NDF &.LDF) files to the new server.
3. Attaching the database on the new server by specifying the new location of the moved files.

In this tip, we go through an example to detach and attach the AdventureWorks database using SQL Server Management Studio and also using TSQL commands.

Detaching a Database Using SQL Server Management Studio

1. Connect to SQL Server 2005 Instance using SQL Server Management Studio.
2. Execute the below TSQL code to find the location of the .MDF and .LDF files for the AdventureWorks database in the SQL Server 2005 environment.

USE AdventureWorks
GO
sp_helpfile 
GO

use the detach and attach operation to upgrade the user database from SQL Server 2005 to SQL Server 2008

3. In Object Explorer, expand Databases and then right click the AdventureWorks database and choose the Tasks -> Detach... option from the drop down menu as shown below.

Detaching a Database Using SQL Server Management Studio

4. In the Detach Database window, select Drop Connections option and then click OK to disconnect all the existing connections from the database and to successfully detach the AdventureWorks database.

select Drop Connections option

Detaching a Database Using TSQL Command

Execute the below TSQL code to detach the AdventureWorks database.

USE [master]
GO

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

EXEC dbo.sp_detach_db @dbname = N'AdventureWorks', @keepfulltextindexfile=N'true'
GO

Once you have detached the database from the SQL Server 2005 environment, the next step will be to copy the database related .MDF and .LDF files to the SQL Server 2008 environment. Let us go through the steps to attach the AdventureWorks database in SQL Server 2008.

Attaching a Database Using SQL Server Management Studio

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio.

2. In the Object Explorer, right click Databases and then choose Attach... option from the drop down menu as shown below.

Connect to SQL Server 2008 Instance using SQL Server Management Studio

3. This will open up an Attach Databases window as shown in the snippet below.

open up an Attach Databases window

4. In the Attach Databases window click on the Add... button as highlighted in the above snippet to open the Locate Databases Files window.

5. In Locate Databases Files window, you need to browse and locate the MDF file that you just copied as shown in the below snippet and click OK.

open the Locate Databases Files window

6. In the Attach Databases window, you will be able to see that SQL Server 2008 has automatically identified the transaction log file of AdventureWorks database as shown in the below snippet, if not you will need to locate the .LDF file you copied as well. Finally to attach the database click OK.

see that SQL Server 2008 has automatically identified the transaction log file

7. Once the database is attached to the SQL Server 2008 instance it will be upgraded automatically.

Attaching a Database Using TSQL

Execute the below TSQL code to attach the AdventureWorks database on an instance of SQL Server 2008. The filename is the file and location where the files were copied.

USE [master]
GO
CREATE DATABASE [AdventureWorks] ON 
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks_Data.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\AdventureWorks_Log.ldf' )
 FOR ATTACH
GO

Changing Compatibility Level

Once the database is upgraded to SQL Server 2008 execute the below TSQL code to change the database compatibility to 100 (SQL 2008). You may need to check that your application works without issue after changing the compatibility level.

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100
GO

Verify Logical and Physical Integrity of Database

DBAs can check the logical and physical integrity of all the objects within the database by executing a DBCC CHECKDB. In our case we are using the "AdventureWorks" database.

DBCC CHECKDB ('AdventureWorks')
GO

Next Steps

Before upgrading you may want to run the SQL 2008 Upgrade Advisor
Read these related tips:
- SQL Server 2008 Upgrade Plan for DBAs
- Upgrades

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, June 12, 2012 - 12:55:56 AM - Ashish Kumar Mehta

I am afraid you cannot detach a database from SQL Server 2008 and attach the database to SQL Server 2005. This is not supported in SQL Server. The reason being once you have attach a database to a higher version the database files are upgraded to the new version internally in you case its is version 611. Once its upgraded you cannot downgrade it to version supported by SQL Server 2005.

You need to restore the database on SQL Server 2005 using the backups and make the required changes.

Hope this helps!

Wednesday, June 6, 2012 - 6:40:25 PM - jonathan

In my situation, I need to preserve the database version. The scenario is that I have a database that is created in 2005 instance (database version 11) I attached this database to a SQL 2008 instance and execute some DDL to create tables, etc. Now, I want to able to attach this database back to SQL 2005 instance so that my previous versions of apps can still access the data.

While it was still connected I ran the following commands:

DBCC TRACEON (3604)
GO

DBCC PAGE ('pioneer',1,9,3)
GO

DBCC TRACEOFF (3604)
GO

and verify that dbi_createVersion is 611.

But I can't attached this mdf file to my original 2005 instance with the following message:

The database cannot be opened because it is version 661. This server supports version 611 and earlier. A downgrade path is not supported.

Thursday, October 7, 2010 - 4:55:39 AM - [email protected]

Sometimes you can get a complaint from SQL when attempting the attach, particularly on Windows 7. One time this will almost certainly occur is when you detach, uninstall and reinstall SQL (due to an error or to change server collation etc) and then try to re-attach.

If you get this error then check the permissions on the file you are attaching and make sure that the current SQL user has access to it (or the folder its in). Also make sure to run the management studio as administratior (right click the management studio and choose run as administrator). Once the DB is attached this will no longer be an issue and you can connact normally.

Finally, if you still cannot get in then try a reboot as the file may be locked by the earlier process, even though it no longer exists, This doesn't really make sense but it does happen. I think the golden rule here for rebuilding a SQL server is to always reboot before you reinstall just to make sure all file locks are relinguished.