Saturday, April 25, 2015

How do you detach a database from a SQL Server Instance?

There are a couple of ways you can do this. Let us say you are not into coding. Then the easiest way is to use the SQL Server Management Studio as described in this post.

Detaching a database
Let us say we want to detach database Feb6 from the Server Hodentek8\Regency Park
Observe that a query has fun on the database

detach01

Right click and click Detach.. in the Tasks pop-up menu.
Detach Database window appears as shown

detach02

Click OK. After some processing the detach window appears again indicating an error as shown.
detach03

In order to detach, the database should not be in use. Once the database is a target of a query it is in use.

Now close the query window. Click on Detach... menu item as before.
The Detach Database window appears as before.
Now click OK.
You do not get any message the detach window will close, but you can see that the database  Feb6 has disappeared from the Object Explorer.
You will find the detached files Feb6.mdf and Feb6_log.ldf in the following location on your computer:
C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA

If you want to re-attach it with Windows PowerShell read here:
http://hodentekmsss.blogspot.com/2015/04/attaching-detached-database-in-sql.html

No comments: