Summary: in this tutorial, you will learn how to delete a database in a SQL Server instance using the
DROP DATABASE statement and SQL Server Management Studio.
Note that this tutorial uses the
SampleDb created in the
CREATE DATABASE tutorial for the demonstration.
Using the SQL Server
DROP DATABASE statement to delete a database
To remove an existing database from a SQL Server instance, you use the
DROP DATABASE statement.
DROP DATABASE statement allows you to delete one or more databases with the following syntax:
DROP DATABASE [ IF EXISTS ]
In this syntax, you specify the name of the database that you want to drop after the
DROP DATABASE keywords. If you want to drop multiple databases using a single statement, you can use a comma-separated list of database names after the
DROP DATABASE clause.
IF EXISTS option is available from SQL Server 2016 (13.x). It allows you to conditionally delete a database only if the database already exists. If you attempt to delete a nonexisting database without specifying the
IF EXISTS option, SQL Server will issue an error.
Before deleting a database, you must ensure the following checked points:
- First, the
DROP DATABASEstatement deletes the database and also the physical disk files used by the database. Therefore, you should have a backup of the database in case you want to restore it in the future.
- Second, you cannot drop the database that is currently being used.
Trying to drop a database currently being used causes the following error:
Cannot drop database "database_name" because it is currently in use.
The following example uses the
DROP DATABASE statement to delete the
DROP DATABASE IF EXISTS TestDb;
Using the SQL Server Management Studio to drop a database
You can follow these steps to delete the
First, right-click on the database name that you want to delete and choose Delete menu item:
Second, uncheck the Delete backup and restore history information for databases check box, check the Close existing connections check box, and click the OK button to delete the database.
Third, verify that the database has been dropped from the Object Explorer.
In this tutorial, you have learned how to use the SQL Server
DROP DATABASE statement and SQL Server Management Studio to delete databases in an SQL Server instance.