Skip to content

detach-a-database.md

Latest commit

 

History

History
113 lines (75 loc) · 4.9 KB

detach-a-database.md

File metadata and controls

113 lines (75 loc) · 4.9 KB
title Detach a Database | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic article
f1_keywords
sql13.swb.detachdatabase.f1
helpviewer_keywords
database detaching [SQL Server]
detaching databases [SQL Server]
ms.assetid f63d4107-13e4-4bfe-922d-5e4f712e472d
caps.latest.revision 36
author JennieHubbard
ms.author jhubbard
manager jhubbard

Detach a Database

This topic describes how to detach a database in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. 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

Limitations and Restrictions

For a list of limitations and restrictions, see Database Detach and Attach (SQL Server).

Security

Permissions

Requires membership in the db_owner fixed database role.

Using SQL Server Management Studio

To detach a database

  1. In [!INCLUDEssManStudioFull] Object Explorer, connect to the instance of the [!INCLUDEssDEnoversion] and then expand the instance.

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

  3. 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 [!INCLUDEssVersion2005].

    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.

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

Using Transact-SQL

To detach a database

  1. Connect to the [!INCLUDEssDE].

  2. From the Standard bar, click New Query.

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

See Also

Database Detach and Attach (SQL Server)
sp_detach_db (Transact-SQL)