How to detach a database in SQL Server
This topic describes how to detach a database.
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. You can later use these files to attach or move the database to any instance of SQL Server, including the server from which the database was detached.
Prerequisites
Before detaching a database, ensure:
- If a database is replicated, unpublish it and disable publishing.
- If there are database snapshots on the database, drop all of them.
- If a database is mirrored in a database mirroring session, terminate it.
- If a database is suspect, first put it into emergency mode.
- A system database cannot be detached.
Detach a database
1. Connect to a server instance.
2. In Database Explorer, right-click the database you want to detach and select Tasks > Detach Database.
3. In the Detach Database dialog, select the checkboxes next to the following options:
- Drop Connections to disconnect connections to the specified database.
Note
You cannot detach a database with active connections.
- Update Statistics to update the existing optimization statistics.
Note
By default, the detach operation retains any outdated optimization statistics.
4. To save the changes, click OK.
The database gets the 
icon, indicating that the database was detached.
5. To close the dialog, click Close.
Detach multiple databases
1. In Database Explorer, hold down Ctrl and select the required databases.
2. On the shortcut menu, select Tasks > Detach Database.
3. In the Detach Database dialog, select the checkboxes next to the required options, then click OK.
4. To close the dialog, click Close.
Errors during a detach operation
If the database fails to detach, hover over the error message in the Status column to view details. The message also provides guidance on resolving the issue and completing the detach operation.
