DROP DATABASE (Transact-SQL)

Removes one or more databases or database snapshots from an instance of SQL Server.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DROP DATABASE { database_name | database_snapshot_name } [ ,...n ] 
[;]

Arguments

  • database_name
    Specifies the name of the database to be removed. To display a list of databases, use the sys.databases catalog view.
  • database_snapshot_name
    Specifies the name of a database snapshot to be removed.

Remarks

To use DROP DATABASE, the database context of the connection cannot be the same as the database or database snapshot to be dropped.

The DROP DATABASE statement must run in autocommit mode and is not allowed in an explicit or implicit transaction. Autocommit mode is the default transaction management mode. For more information, see Autocommit Transactions.

Dropping a Database

System databases cannot be dropped.

Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

You cannot drop a database currently being used. This means open for reading or writing by any user. To remove users from the database, use ALTER DATABASE to set the database to SINGLE_USER.

Any database snapshots on a database must be dropped before the database can be dropped.

If the database is involved in log shipping, remove log shipping before dropping the database. For more information, see Log Shipping.

A database can be dropped regardless of its state: offline, read-only, suspect, and so on. To display the current state of a database, use the sys.databases catalog view.

A dropped database can be re-created only by restoring a backup. Database snapshots cannot be backed up and, therefore, cannot be restored.

When a database is dropped, the master database should be backed up.

Dropping a Database Snapshot

Dropping a database snapshot deletes the database snapshot from an instance of SQL Server and deletes the physical NTFS File System sparse files used by the snapshot. For information about using sparse files by database snapshots, see How Database Snapshots Work.

Dropping a database snapshot 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.

Dropping a Database Used in Replication

To drop a database published for transactional replication, or published or subscribed to merge replication, you must first remove replication from the database. For more information about how to remove replication from a database, see Removing Replication. If a database is damaged or replication cannot first be removed or both, in most cases you still can drop the database by using ALTER DATABASE to set the database offline and then dropping it.

Permissions

To execute DROP DATABASE, at a minimum, a user must have CONTROL permission on the database.

Examples

A. Dropping a single database

The following example removes the Sales database.

DROP DATABASE Sales;

B. Dropping multiple databases

The following example removes each of the listed databases.

DROP DATABASE Sales, NewSales;

C. Dropping a database snapshot

The following example drops a database snapshot, named sales_snapshot0600, without affecting the source database.

DROP DATABASE sales_snapshot0600;

See Also

Reference

ALTER DATABASE (Transact-SQL)
CREATE DATABASE (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.databases (Transact-SQL)

Other Resources

Deleting a Database
Dropping a Database Snapshot

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006

New content:
  • Added information about clearing the plan cache to the "Dropping a Database Snapshot" section.

17 July 2006

New content:
  • Added the reference to using sp_detach_db as an alternative to dropping a database.