Detaches a database that is currently not in use from a server instance and, optionally, runs UPDATE STATISTICS on all tables before detaching.
For a replicated database to be detached, it must be unpublished. For more information, see the "Remarks" section later in this topic.
When a database is detached, all its metadata is dropped. If the database was the default database of any login accounts, master becomes their default database.
For information about how to view the default database of all the login accounts, see sp_helplogins (Transact-SQL). If you have the required permissions, you can use ALTER LOGIN to assign a new default database to a login.
A database cannot be detached if any of the following are true:
The database is currently in use. For more information, see "Obtaining Exclusive Access," later in this topic.
If replicated, the database is published.
Before you can detach the database, you must disable publishing by running sp_replicationdboption.
If you cannot use sp_replicationdboption, you can remove replication by running sp_removedbreplication.
A database snapshot exists on the database.
Before you can detach the database, you must drop all of its snapshots. For more information, see How to: Drop a Database Snapshot (Transact-SQL).
A database snapshot cannot be detached or attached.
The database is being mirrored.
The database cannot be detached until the database mirroring session is terminated. For more information, see Removing Database Mirroring.
The database is suspect.
In SQL Server 2005 and later versions, you must put a suspect database into emergency mode before you can detach the database. For more information about how to put a database into emergency mode, see ALTER DATABASE (Transact-SQL).
The database is a system database.
Obtaining Exclusive Access
Detaching a database requires exclusive access to the database. If the database that you want to detach is in use, before you can detach it, set the database to SINGLE_USER mode to obtain exclusive access.
For example, the following ALTER DATABASE statement obtains exclusive access to the AdventureWorks database after all current users disconnect from the database.
USE master; ALTER DATABASE AdventureWorks SET SINGLE_USER; GO
To force current users out of the database immediately or within a specified number of seconds, also use the ROLLBACK option: ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK rollback_option. For more information, see ALTER DATABASE (Transact-SQL).
Reattaching a Database
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.
The following example detaches the AdventureWorks database with skipchecks set to true.
EXEC sp_detach_db 'AdventureWorks', 'true';
The following example detaches the AdventureWorks database and keeps the full-text index files and the metadata of the full-text index. This command runs UPDATE STATISTICS, which is the default behavior.
exec sp_detach_db @dbname='AdventureWorks' , @keepfulltextindexfile='true';