How to Drop Database in SQL Server

DROP DATABASE in SQL Server lets you remove one or many databases.

Understand the “drop database” and How It Works

DROP DATABASE in SQL Server removes a database from the server. The system deletes the physical files after this action.

This command runs fast and needs no manual file removal. Users must close all active sessions before running it to stop errors.

Use this command to delete one or more databases from SQL Server.

DROP DATABASE database_name;

You can also drop many databases at once.

DROP DATABASE database_name1, database_name2;

A user must own or have permission on the database before deletion. All active connections must close to prevent lock errors.

The database cannot act as a system database. You must back up your data because the action cannot be reversed.

The server may show errors if other users connect to the database. It may also block deletion when the database has replication or log shipping.

Use this:

ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

To force disconnection.

Examples of Dropping a Single and Multiple Databases

Drop a Single Database:

DROP DATABASE SalesDB;

This example deletes a database named SalesDB. It runs only if no active connection exists. Users must back up before this action because it deletes data permanently.

Drop Multiple Databases at Once:

DROP DATABASE HRDB, FinanceDB;

This example removes two databases from the server in one command. It saves time but deletes all data at once. Make sure to close all sessions and back up data before this command.

Force Close Connections Before Drop:

ALTER DATABASE ReportDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ReportDB;

This example forces the disconnection of all users before deletion. It helps remove a database when a normal drop fails due to active sessions. Always back up data before using it.

Wrapping Up

You learned what the drop database command in SQL Server does and how to run it.

Here is a quick recap:

• The drop database removes one or many databases from SQL Server.
• Close all connections before deletion to prevent errors.
• Back up your data because the command deletes files permanently.
• Use force disconnection only when the normal drop fails.

FAQs

DROP DATABASE database_name;

DROP DATABASE db1, db2, db3;

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE database_name;

Similar Reads