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
.
The Detach
dialog box appears.
In 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
.
If You would like to attach this database, right click the Database
node and from pop-up menu choose Attach
.
The Attach
dialog box appear.
Click 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.
And 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.
Go to next step clicking the OK
button. In Object Explorer
window. You see in Databases
node AdventureWorks2012
database.
Another 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