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

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, or GUI.

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.

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 using the standard SQL Server Drop database database statement.

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

	WHERE NAME='MyDatabase'

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.

Be the first to comment

Leave a Reply

Your email address will not be published.