How attach or detach database in SQL Server Management Studio?

Sometime we don’t want to show database in Databases node in SQL Server Management Studio, because we needn’t  to change something in it. But main reason is move database from one to another server.

Resolution for it is the Detach task. To do it right click on name of your database in Databases node and choose Task->Detach.

qq1The Detach dialog box appears.

qq2Click the OK button.

qq4In Object Explorer window in Databases will not be this database. It physical be in Data catalogue of SQL Server, but don’t visibility in SQL Server Management Studio.

qq5
If You would like to attach this database, right click the Database node and from pop-up menu choose Attach.
sam4
The Attach dialog box appear.
sam5Click in it the Add button. In Locate Database File window You see all mdf files of databases in your SQL Server. Select your pasted database.
sam6And click the OK button. In Attach Database . If this database existed in this place, the row with ldf file will be OK, If not( not found is in message) remove this row ( log type ) with ldf file, SQL server will create one later, the database is attached.

qqq

Go to next step clicking the OK button. In Object Explorer window.  You see in Databases node AdventureWorks2012 database.
sam9Another way to attach databse with mdf file is use the T-SQL query.

USE master
GO
CREATE DATABASE [AdventureWorks2012test] ON
( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')
FOR ATTACH
GO

In this script it will be attach AdventureWorks2012 database with new name: AdventureWorks2012test. If you don’t have ldf file, the file will be created after attach.
Below SQL script if you have either LDF file:

USE master
GO
CREATE DATABASE [AdventureWorks2012test] ON
( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf'),
( FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf')
FOR ATTACH
GO