DROP DATABASE

Last updated: May 22, 2023

Removes one or more databases from a SQL Server instance.

Syntax

DROP DATABASE [ IF EXISTS ] <database_name> [ ,...n ] ;

Arguments

IF EXISTS

When specified, only used if the database already exists. If the database doesn't exist, it's not an error and no action is performed.

<database_name>

Specifies the name of the database to be dropped. Database names must follow the rules for identifiers.

Permissions

Requires CONTROL permission on the database, or alternatively, ALTER ANY DATABASE permission.

Important

Dropping a database is a destructive operation. Ensure you have a valid backup before proceeding. Once a database is dropped, all its objects, data, and logs are permanently deleted and cannot be recovered without a backup.

Examples

A. Dropping a single database

The following example drops the database named MySampleDatabase.

DROP DATABASE MySampleDatabase;

B. Dropping multiple databases

The following example drops two databases: SalesData and Inventory.

DROP DATABASE SalesData, Inventory;

C. Dropping a database only if it exists

The following example drops the database named TempDB_Old, but only if it already exists. If it doesn't exist, the statement completes without error.

DROP DATABASE IF EXISTS TempDB_Old;

Tip

You can check if a database exists using the sys.databases catalog view before attempting to drop it.

Notes

  • A database cannot be dropped if it is in use. If other users are connected to the database, the DROP DATABASE statement will fail. You may need to disconnect all active connections first.
  • You can use the ALTER DATABASE statement with the SET RESTRICTED_USER option to prevent new connections and disconnect existing ones before dropping the database.
  • Dropping a database removes all associated files (data and log files).

See Also