This topic describes how to detach a database that is currently not in use. Detaching a database removes it from the instance of the Microsoft SQL Server Database Engine but leaves intact the database, with its data files and transaction log files.
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.
-
Detaching a database requires exclusive access to the database. If the database is in use, restrict access to a single user:
-
Right-click the database name and point to Properties.
-
In the Select a page pane, select Options.
-
In the Other options pane, scroll down to the State options.
-
Select the Restrict Access option, and in its drop-down list, select Single.
-
Click OK.
A message box appears to inform you that this action will close all connections to the database. To proceed, click OK.
-
Right-click the database name, point to Tasks, and then click Detach. The Detach Database dialog box appears.
-
The Databases to detach grid displays the name of the selected database in the Database Name column. Verify that this is the database you want to detach.
-
By default, the detach operation retains any out-of-date optimization statistics when detaching the database; to update the existing optimization statistics, click the Update Statistics check box.
-
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.
-
The Status column displays the current database state (either Ready or Not Ready).
If the status is Not Ready, the Message column displays hyperlinked information about the database. When a database is involved with replication, the Message column displays Database replicated. When a database has one or more active connections, the Message column displays <number_of_active_connections> Active connections; for example, 1 Active connection(s). Before you can detach the database, you must disconnect any active connections by selecting the Drop Connections check box.
To obtain more information about a message, click the hyperlink.
-
When you are ready to detach the database, click OK.
.gif) 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.
|
See Also