Attach and Detach of Databases in SQL Server

I have seen some of general queries regarding attach and detach of databases in SQL server (MSDN) forums. This inspired me to write this blog, so that it will be useful for others as well.

Let me also give some background of attach and detach is used generally. This is used as one of the option in moving databases from one SQL server database to another.

Attach and Detach of databases can be done in three methods (SQL Server Management Studio, System Stored Procedures, Using Create database command) in SQL server.

1. Using SQL Server Management Studio is simple and straight forward method which can be done by right     clicking on database and selecting attach method and then by adding the mdf file from operating system location.

2. System stored procedures sp_attach_db and sp_detach_db can be used for attaching and detaching the databases. Only 16 files can be attached by using this method. This is method is not supporting in future versions of SQL server. Microsoft recommends to use CREATE DATABASE method.

Set the database in single user mode

ALTER DATABASE AdventureWorks2012_1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO

Issue the detach mode command

EXEC SP_DETACH_DB @DBNAME=N'ADVENTUREWORKS2012_1'
GO

EXEC SP_ATTACH_DB @dbname = N'AdventureWorks2012_1',
@filename1 ='C:\TEST\AdventureWorks2012_Data.MDF'
GO

3. CREATE DATABASE method which will the option supported in future versions of SQL Server. In this example, I don't have log (LDF) file to attach to the database, hence I have use "ATTACH_REBUILD_LOG" option. You can use "FOR ATTACH" option if you have all required files for the attach.

USE MASTER

GO

CREATE DATABASE ADVENTUREWORKS2012 (FILENAME='C:\Adventureworks_2012\AdventureWorks2012_Data.MDF' ) FOR ATTACH_REBUILD_LOG

GO

Message after attach the database:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect. New log file 'C:\Adventureworks_2012\ADVENTUREWORKS2012_log.ldf' was created Converting database 'ADVENTUREWORKS2012' from version 705 to the current version 706. Database 'ADVENTUREWORKS2012' running the upgrade step from version 705 to version 706.