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.
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
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.
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.
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.
3. This will open up an Attach Databases window as shown in the snippet below.
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.
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.
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
- Before upgrading you may want to run the SQL 2008 Upgrade Advisor
- Read these related tips:
Last Update: 2010-10-06
About the author
View all my tips