How to: Detach a Database (SQL Server Management Studio)

  • Article

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.

Note

After detaching a SQL Server 2005 database, you can reattach it to the same or another instance of SQL Server 2005. For more information, see How to: Attach a Database (SQL Server Management Studio).

To detach a database

  1. In SQL Server Management Studio Object Explorer, connect to the instance of the SQL Server Database Engine and then expand the instance.

  2. Expand Databases, and select the name of the user database you want to detach.

  3. 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.

  4. Right-click the database name, point to Tasks, and then click Detach. The Detach Database dialog box appears.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

See Also

Tasks

How to: Attach a Database (SQL Server Management Studio)

Concepts

Detaching and Attaching Databases
Securing Data and Log Files

Other Resources

SQL Server Management Studio Tutorial

Help and Information

Getting SQL Server 2005 Assistance