Moving Database from SQL Server 2005 to Higher Versions(Detach &Attach Method)

Detach and Attach method to move database from sql 2005 to 2008

Detach:

In this Topic we will see how to Detach Database from SQL Server 2005 to SQL Server  Higher Versions. You can use database Detach and Attach Method option to move database  from one location to another location on the server.

Different ways to Detach Database in SQL Server

• Detach Database in SQL Server Using SQL Server Management Studio (SSMS)
• Detach Database in SQL Server Using TSQL Script

Permissions to Detach Database in SQL Server

You need membership in the db_owner fixed database role to detach database in SQL Server.

Important Note: It is recommend to note down the location of Data and Log file before detaching the SQL Server Database.

TSQL Script to Identify location of Data and Log file of SQL Server Database

USE MyTechMantra
GO

Exec sp_helpfile
GO

How to Detach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Expand Databases node in Object Explorer

3. Right click User Database -> Tasks ->  Detach… option from the drop down list as shown in the snippet below.

4. In Detach Database dialog box, select Drop Connections and Update Statistics check boxes as shown in the snippet below and click OK to detach the user database from SQL Server. You won’t be able to detach a database until all the connection to the database is dropped. Refer the following article titled “How to Get Exclusive Access to SQL Server Database” to know how to kill all active connections from a database. It is always a best practice to Update Statistics before detaching SQL Server database.

5. Once you have clicked OK the database will be detached from SQL Server.

How to Detach Database in SQL Server Using TSQL Script

Database Administrator can detach database from SQL Server using the below mentioned TSQL script.

USE [master]
GO
ALTER DATABASE [MyTechMantra] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyTechMantra', @skipchecks = 'false'
GO

Attach:

How to Attach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Right click Databases -> Attach… option from the drop down list as shown in the snippet below.

3. In Attach Databases dialog box click on Add… button as shown in the snippet below.

4. In Locate Database Files dialog box select the .MDF file of the database and click OK to return to Attach Database dialog box.

5. In Attach Databases dialog box; under Databases to attach and Database Details you will see all the relevant information with respect to the database which you are attaching in SQL Server. Finally, click OK to attach the database to SQL Server.

How to Attach Database in SQL Server Using TSQL Script

Database Administrator can attach database in SQL Server using the below TSQL script.

USE [master]
GO
CREATE DATABASE [MyTechMantra] ON 
( FILENAME = N'D:\MSSQL\Data\MyTechMantra.mdf' ),
( FILENAME = N'D:\MSSQL\Data\MyTechMantra_log.ldf' )
 FOR ATTACH
GO