SqlTutorial-1 : Lesson-12 : Class-2: Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Attaching and De-Attaching Databases

Attaching database adds the database to instance of SQL server, and makes it accessible. Detaching database removes the database from an instance of SQL Server and leaves the database file intact (does not change/deleted). When the database has been detached, the files associated with the database (i.e, .mdf, .ndf, and .ldf files) can be copied to an alternate location. You can further reattach them.

• Attaching and Detaching are convenient ways to move or copy database files
• Attaching and detaching is extremely fast. It is therefore a good alternative for BACKUP and RESTORE.

Attaching db-file is mundane task while deploying Local-DB DataDrivenApplication. For example,  while developing Asp.Net application with local db-file, you must attach the Db-file located at App_Data folder to Database Server.

1. Attaching Databases

You can create database with existing .mdf file. If .mdf and .ldf files already you have, then just attach these files using FOR ATTACH clause.

Examples

CREATE DATABASE [myDb] ON 
   (FILENAME=’C:\xyz\App_Data\database.mdf’), 
   (FILENAME=’ ’C:\xyz\App_Data\database_log.ldf’) 
 FOR ATTACH

2. Detaching database;

Examples

EXEC master.dbo.sp_dettach_db
   @dbname=N’copyOfdatabase’, @keepfulltextindexfile=’false’
GO

3. Using SqlServer Management Studio (SSMS)

SSMS-Attaching

For attaching right click on database then select Attach file
 

SSMS-Dettaching

For detaching right click on database then select tasks detach file;

Note: Visual Studio IDE doesn’t support.