SQL guide · Tables, views & databases (DDL)

SQL DROP DATABASE: How to Delete a Database (and How to Do It Safely)

The DROP DATABASE statement permanently removes a database together with every table, view, index, stored procedure and row inside it. It is the most destructive common SQL command. There is usually no confirmation prompt and no undo, so once it succeeds the only way back is to restore from a backup. Use it deliberately and never against a server you have not double checked.

Typical legitimate uses are tearing down temporary databases after a test run, cleaning up a development environment, or retiring an application that has been fully migrated elsewhere. In automated pipelines it often appears in a teardown step paired with a matching CREATE DATABASE at the start. Because the same command in the wrong place can wipe production, many teams restrict the privilege so that application accounts cannot drop anything at all.

Most systems refuse to drop a database that is currently in use by other sessions, or require you to be connected to a different database when you run the command. You may need to disconnect users first. If you only want to remove the data but keep the structure, TRUNCATE TABLE or DELETE on individual tables is a far safer choice, and if you are not certain, take a backup before dropping anything.

Basic syntax

DROP DATABASE [IF EXISTS] database_name;

-- SQL Server allows several at once
DROP DATABASE [IF EXISTS] db1, db2;

The only required value is the database name. IF EXISTS suppresses the error that would otherwise appear when the database is missing, which keeps repeatable scripts from failing. There is no CASCADE option because everything inside the database goes with it.

SQL DROP DATABASE examples

Drop a database

The plain form deletes the database named shop_test and everything it contains. Run it only when you are certain of the name and the server you are connected to, since the data is gone as soon as it completes.

DROP DATABASE shop_test;

Drop only if it exists

Adding IF EXISTS makes the statement succeed quietly when there is nothing to remove. That is useful in setup scripts that tear down and rebuild a scratch database every time they run.

DROP DATABASE IF EXISTS shop_test;

Back up first, then drop

A careful sequence takes a backup before the destructive step, so a mistake can be reversed. This SQL Server example writes a full backup to disk and only then removes the database, and a similar order applies on other systems with their own backup tools.

BACKUP DATABASE shop_old
TO DISK = 'D:\backups\shop_old_final.bak';

DROP DATABASE shop_old;

Disconnect users before dropping in SQL Server

A database with open connections cannot be dropped. Setting it to single-user mode with an immediate rollback closes other sessions so the drop can proceed, which is appropriate only for databases you own and are retiring.

ALTER DATABASE shop_old
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE shop_old;

Where SQL dialects differ

PostgreSQL: Supports IF EXISTS. You cannot drop the database you are connected to, and active connections block the drop. From PostgreSQL 13 you can add WITH (FORCE) to terminate other sessions. Requires ownership of the database.

MySQL: DROP SCHEMA is a synonym. IF EXISTS is supported. The statement removes the database directory and every table in it, and it is not transactional, so it cannot be rolled back.

SQL Server: Supports IF EXISTS from SQL Server 2016 and multiple databases in one statement. You cannot drop a system database or one that is in use. Dropping does not delete backup files already written to disk.

SQLite: There is no DROP DATABASE. A database is a file, so you delete it with the operating system, or call DETACH DATABASE to disconnect an attached one without deleting it.

Oracle: Dropping a whole database is an administrator operation done with DROP DATABASE while mounted in restricted mode. Most developers instead remove a schema with DROP USER ... CASCADE.

Common mistakes

  • Running the command against the wrong server or connection. Always confirm the host and database name in your client before executing.
  • Dropping without a recent verified backup. Dropped databases are not recoverable from the database itself.
  • Trying to drop a database while connected to it or while other sessions are open, then being surprised by the error. Connect elsewhere and close other sessions first.
  • Granting the drop privilege to application accounts. Keep it with administrators so a bug or injected statement cannot delete everything.
  • Using DROP DATABASE when you only wanted to empty tables. TRUNCATE TABLE or DELETE keeps the structure and is much less destructive.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator