Delete 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 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.

Here are two ways to create a new database, the first one is to create the ms sql database with a script or by using the GUI and SSMS. There are several ways to delete a database, either via the graphical interface of SSMS or via a simple SQL query as in this article.

How to delete a SQL Server database with a simple SQL script command?

First of all, this sample code checks if the database exists on the server. Then the second part of the code deletes the database.

Note: Be careful, this command to delete the database is irreversible! Make sure you have the backups before running it.

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

Note also another prerequisite for the SQL command to delete also the files physically on the disk, i.e. the MDF, LDF and BAK 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.

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.