SQL Server DROP DATABASE

Learning Objective

The objective of this SQL Server tutorial is to teach you how to delete the database using DROP DATABASE T-SQL script and using SQL Server Management Studio.

Note:- this tutorial uses sampleDB and TestDB databases which have been created in our earlier tutorial SQL Server CREATE DATABASE.

SQL Server DROP DATABASE Introduction

As a SQL Server Database Administrator or Developer, you sometimes may need to remove a SQL Server database. There are two simple methods to delete a SQL Server database as below.

  1. Using DROP DATABASE T-SQL Script.
  2. Using SQL Server Management Studio.

1) Delete SQL Server database using DROP DATABASE Statement

In order to remove an existing database in SQL Server, you need to use the DROP DATABASE statement.

SQL Server DROP DATABASE Syntax

The DROP DATABASE statement allows you to drop one or more databases using the following statement.

DROP DATABASE [ IF EXISTS ]
database_name
[,database_name2,…];

In this syntax,

  • DROP DATABASE – Keyword to delete the existing database from SQL Server.
  • IF EXISTS – This option is available from SQL Server 2016. It allows you to delete the database only if the database exists. If you attempt to delete a nonexisting database, SQL Server will give an error.
  • database_name – Name of the existing database, you want to delete from SQL Server.

SQL Server DROP DATABASE Example

In our previous tutorial, we have created two databases name sampleDB and TestDB. In this example, we will drop sampleDB using the DROP DATABASE statement.

Before deleting a database, you should remember below points.

  1. DROP DATABASE will remove the database along with it’s physical disk file. So make sure to take backup the database before dropping it.
  2. You cannot drop a database if the database is being used by other sessions.

If you try to drop an existing database which is currently being used by other users, SQL Server gives the following error.

Cannot drop database "sampleDB" because it is currently in use.

The following example will delete the sampleDB database from the SQL Server. 

DROP DATABASE IF EXISTS sampleDB;

2) Delete SQL Server database using SQL Server Management Studio

Now, we will delete the other database named TestDB using SQL Server Management Studio.

You can follow the below steps to delete a existing database from SQL Server using SQL Server Management Studio.

a) First, right click on the Database name which you want to delete and click on Delete option.

sql server drop database

b) Second, select the checkbox Close Existing Connections to drop existing connections before deleting the database and click OK to drop the database from SQL Server.

sql server drop database

c) Third, by Selecting “Delete backup and restore history information for databases“, you will able to delete the backup and restore history which is stored in msdb system database;

d) Fourth, verify the dropped database not exist from object explorer.

sql server object explorer

Summery

In this tutorial, you have learned how to delete a database using the DROP DATABASE statement and SQL Server Management Studio.

Advertisement