Detach Database in SQL Server


Jun 18, 2012

Introduction

In this article we will see how to Detach Database in SQL Server 2008. You can use database Detach and Attach Method option to move database files from one location to another location on the server. However, the steps mentioned in this article are applicable on SQL Server 2005 and higher versions. To know more about how to Attach Database in SQL Server refer the following article “Attach Database in SQL Server”.

Different ways to Detach Database in SQL Server

  • Detach Database in SQL Server Using SQL Server Management Studio (SSMS)
  • Detach Database in SQL Server Using TSQL Script

Permissions to Detach Database in SQL Server

You need membership in the db_owner fixed database role to detach database in SQL Server.

Important Note: It is recommend to note down the location of Data and Log file before detaching the SQL Server Database.

TSQL Script to Identify location of Data and Log file of SQL Server Database

USE MyTechMantra
GO

Exec sp_helpfile
GO

How to Detach Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio

2. Expand Databases node in Object Explorer

3. Right click User Database -> Tasks ->  Detach… option from the drop down list as shown in the snippet below.

Detach Database in SQL Server Using SSMS

4. In Detach Database dialog box, select Drop Connections and Update Statistics check boxes as shown in the snippet below and click OK to detach the user database from SQL Server. You won’t be able to detach a database until all the connection to the database is dropped. Refer the following article titled “How to Get Exclusive Access to SQL Server Database” to know how to kill all active connections from a database. It is always a best practice to Update Statistics before detaching SQL Server database.

Detach Database in SQL Server Using SQL Server Management Studio

5. Once you have clicked OK the database will be detached from SQL Server.

How to Detach Database in SQL Server Using TSQL Script

Database Administrator can detach database from SQL Server using the below mentioned TSQL script.

USE [master]
GO
ALTER DATABASE [MyTechMantra] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'MyTechMantra', @skipchecks = 'false'
GO

Conclusion

In this article you have seen the steps to detach a database from SQL Server. You can use this option to temporarily remove a database from an instance of SQL Server. It is always recommended to use Database Backup and Restore option to move a database form one server to another server.

Continue Free Learning...
  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."