Attach and Detach Databases

Attach and Detach Databases

When to use attach and detach database operation

1.)    If you are planning the migration of a database from SQL Server 2008 to SQL Server 2012.

2.)    If you need to upgrade a database

3.)    If you need to move a DB from server to server

When attaching and detaching a database following points are important
The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, attach works across 32-bit and 64-bit environments. A database detached from a server instance running in one environment can be attached on a server instance that runs in another environment.

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.

Detach a Database
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

Detaching a Database Using TSQL Command

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

Attaching a Database

Attaching a Database using T-SQL

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

USE [master]

GO

ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100

GO