.png)
Detach a Database
This topic describes how to detach a database in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. The detached files remain and can be reattached by using CREATE DATABASE with the FOR ATTACH or FOR ATTACH_REBUILD_LOG option. The files can be moved to another server and attached there.
In This Topic
-
Before you begin:
-
To detach a database, using:
Limitations and Restrictions
For a list of limitations and restrictions, see Database Detach and Attach (SQL Server).
Security
To detach a database
-
In SQL Server Management Studio Object Explorer, connect to the instance of the SQL Server Database Engine and then expand the instance.
-
Expand Databases, and select the name of the user database you want to detach.
-
Right-click the database name, point to Tasks, and then click Detach. The Detach Database dialog box appears.
- Databases to detach
-
Lists the databases to detach.
- Database Name
-
Displays the name of the database to be detached.
- Drop Connections
-
Disconnect connections to the specified database.
Note
You cannot detach a database with active connections.
- Update Statistics
-
By default, the detach operation retains any out-of-date optimization statistics when detaching the database; to update the existing optimization statistics, click this check box.
- Keep Full-Text Catalogs
-
By default, the detach operation keeps any full-text catalogs that are associated with the database. To remove them, clear the Keep Full-Text Catalogs check box. This option appears only when you are upgrading a database from SQL Server 2005.
- Status
-
Displays one of the following states: Ready or Not ready.
- Message
-
The Message column may display information about the database, as follows:
-
When a database is involved with replication, the Status is Not ready and the Message column displays Database replicated.
-
When a database has one or more active connections, the Status is Not ready and the Message column displays <number_of_active_connections> Active connection(s) — for example: 1 Active connection(s). Before you can detach the database, you need to disconnect any active connections by selecting Drop Connections.
To obtain more information about a message, click the hyperlinked text to open Activity Monitor.
-
-
When you are ready to detach the database, click OK.
|
Note
|
|---|
|
The newly detached database will remain visible in the Databases node of Object Explorer until the view is refreshed. You can refresh the view at any time: Click in the Object Explorer pane, and from the menu bar select View and then Refresh. |
To detach a database
-
Connect to the Database Engine.
-
From the Standard bar, click New Query.
-
Copy and paste the following example into the query window and click Execute. This example detaches the AdventureWorks2012 database with skipchecks set to true.
EXEC sp_detach_db 'AdventureWorks2012', 'true';
