Detach Database in SQL Server
Jun 18, 2012
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.
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.
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
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...
Geeks who read this article also read…
- How to Attach Database in SQL Server
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Change Default SQL Server Backup Folder in SQL Server 2008
- How to Change the default location of SQL Server Data and Log files
- Configuring Database Instant File Initialization Feature of SQL Server
- Database Backup Compression Feature In SQL Server 2008
- New Features in Microsoft SQL Server 2012 for Database Developers
- New Features in Microsoft SQL Server 2012 for Database Administrators
- Using WITH RESULT SETS Feature of SQL Server 2012
- SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012
- Microsoft SQL Server 2008 R2 Build Versions
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- How to Downgrade SQL Server Database from a higher version to a lower version
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to identify if the database was upgraded from a previous version of SQL Server
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- New Features in Microsoft SQL Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- Auditing SQL Server Logins
- Read More SQL Server Articles…