Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2005
 sp_detach_db (Transact-SQL)
SQL Server 2005 Books Online (September 2007)
sp_detach_db (Transact-SQL)

Updated: 1 February 2007

Detaches a database that is currently not in use from a server instance and, optionally, runs UPDATE STATISTICS on all tables before detaching.

Important:
For a replicated database to be detached, it must be unpublished. For more information, see the "Remarks" section later in this topic.

Topic link icon Transact-SQL Syntax Conventions

sp_detach_db [ @dbname= ] 'database_name' 
    [ , [ @skipchecks= ] 'skipchecks' ] 
    [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]
[ @dbname = ] 'database_name'

Is the name of the database to be detached. database_name is a sysname value, with a default value of NULL.

[ @skipchecks = ] 'skipchecks'

Specifies whether to skip or run UPDATE STATISTIC. skipchecks is a nvarchar(10) value, with a default value of NULL. To skip UPDATE STATISTICS, specify true. To explicitly run UPDATE STATISTICS, specify false.

By default, UPDATE STATISTICS is performed to update information about the data in the tables and indexes in the SQL Server 2005 Database Engine. Performing UPDATE STATISTICS is useful for databases that are to be moved to read-only media.

[ @keepfulltextindexfile = ] 'KeepFulltextIndexFile'

Specifies that the full-text index file associated with the database that is being detached will not be dropped during the database detach operation. KeepFulltextIndexFile is a nvarchar(10) value with a default of true. If KeepFulltextIndexFile is false, all the full-text index files associated with the database and the metadata of the full-text index are dropped, unless the database is read-only. If NULL or true, full-text related metadata are kept.

0 (success) or 1 (failure)

None

Restrictions

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.
    Note:
    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).
    Note:
    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, 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.

Detaching a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations." This message is logged every five minutes as long as the cache is flushed within that time interval.

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

Requires membership in the db_owner fixed database role.

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

Release History

1 February 2007

New content:
  • Added a section to the Remarks about clearing the plan cache.
Changed Content
  • Clarified the settings of the @keepfulltextindexfile parameter.

17 July 2006

New content:
  • Added the restriction against detaching system databases.

14 April 2006

New content:
  • Added example that uses @keepfulltextindexfile.
Changed Content
  • Changed @KeepFulltextIndexFile to @keepfulltextindexfile.

5 December 2005

New content:
  • Added the restriction that the database cannot be in use and the section about obtaining exclusive access before running sp_detach_db.
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Processing
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker