Drop a SQL Server database with a script

How to delete a SQL Server database with a script?

And delete all associated files? These files are the .mdf, .ndf, .ldf files and also the .bak backup files. Use the SQL Server Drop database command as below. With a check beforehand to avoid errors. It runs the command only if the SQL Server database exists on the given server and instance.

Create a sample database to use the Drop query

Here are two ways to create a new database, the first one is to create the ms sql database with a script or simply by using the SSMS graphic user interface, abbreviated as GUI. Execute this first script to create an empty database to be dropped on the next steps.

CREATE DATABASE MyDatabase;
GO

Script to drop a SQL Server database on all versions

Hence, there are several ways to delete a database, either via the graphical interface of SSMS or via a simple SQL Server Drop database query as in this article. First of all, this sample code checks if the database exists on the server. Then the second part of the code deletes the database using the standard SQL Server Drop database database statement.

Be careful, the following commands to delete the database is irreversible! Make sure you have the backups before running it.

USE MASTER;
GO

IF EXISTS (
	SELECT *
	FROM SYS.SYSDATABASES
	WHERE NAME='MyDatabase'
)
BEGIN
	DROP DATABASE MyDatabase;
END;
GO

Note also another prerequisite for the SQL command to delete also the files physically on the disk, i.e. the MDF (master data file), LDF (log data file) and BAK (backup file) extensions. Indeed, to work, the database needs to be online at the time of the deletion. Finally, after running this command it is possible to check via Windows Explorer that the files have been deleted.

Use Drop database If Exists on SQL Server 2016 version and higher

Starting from SQL Server 2016, it possible to check if the database, or any other droppable object, and drop it with one single line of code.

USE MASTER;
GO

DROP DATABASE IF EXISTS MyDatabase;
GO

To go further, sometimes the delete command does not work because the target database is in use. Here is how to manage the problem and avoid the 3702 error: Cannot drop the database because it is currently in use.

SQL Server Administration tutorials

• Tutorial to install a second instance of SQL Server.
• How to create a SQL database with a script?
• How to empty the MS SQL transaction log and fix the 9002 error?