{"id":6640,"date":"2022-03-05T06:11:00","date_gmt":"2022-03-05T05:11:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=6640"},"modified":"2022-11-10T11:12:05","modified_gmt":"2022-11-10T10:12:05","slug":"drop-database-script","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/dba\/drop-database-script\/","title":{"rendered":"Drop a SQL Server database with a script"},"content":{"rendered":"\n<h4 class=\"has-text-align-center wp-block-heading\"><strong><em>How to delete a SQL Server database with a script?<\/em><\/strong><\/h4>\n\n\n\n<p>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.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_83 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/dba\/drop-database-script\/#create-a-sample-database-to-use-the-drop-query\" >Create a sample database to use the Drop query<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/dba\/drop-database-script\/#script-to-drop-a-sql-server-database-on-all-versions\" >Script to drop a SQL Server database on all versions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/dba\/drop-database-script\/#use-drop-database-if-exists-on-sql-server-2016-version-and-higher\" >Use Drop database If Exists on SQL Server 2016 version and higher<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"create-a-sample-database-to-use-the-drop-query\"><\/span>Create a sample database to use the Drop query<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Here are two ways to create a new database, the first one is to <a href=\"https:\/\/expert-only.com\/en\/dba\/create-a-sql-server-database-with-a-script\/\">create the ms sql database with a script<\/a> or simply <a href=\"https:\/\/expert-only.com\/en\/sql-server-db\/create-a-database-with-sql-server-management-studio\/\">by using the SSMS graphic user interface<\/a>, abbreviated as GUI. Execute this first script to create an empty database to be dropped on the next steps. <\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">CREATE DATABASE MyDatabase;\nGO\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-script-to-drop-a-sql-server-database-on-all-versions\"><span class=\"ez-toc-section\" id=\"script-to-drop-a-sql-server-database-on-all-versions\"><\/span>Script to drop a SQL Server database on all versions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>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 <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/drop-database.html\" target=\"_blank\" rel=\"noreferrer noopener\">Drop database<\/a> database statement. <\/p>\n\n\n\n<p class=\"has-text-align-center\"><strong>Be careful, the following commands to delete the database is irreversible! Make sure you have the backups before running it<\/strong>.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">USE MASTER;\nGO\n\nIF EXISTS (\n\tSELECT *\n\tFROM SYS.SYSDATABASES\n\tWHERE NAME='MyDatabase'\n)\nBEGIN\n\tDROP DATABASE MyDatabase;\nEND;\nGO\n<\/pre>\n\n\n\n<p>Note also another prerequisite for the SQL command to delete also the files physically on the disk, i.e. the <em>MDF<\/em> (master data file), <em>LDF<\/em> (log data file) and <em>BAK<\/em> (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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"use-drop-database-if-exists-on-sql-server-2016-version-and-higher\"><\/span>Use Drop database If Exists on SQL Server 2016 version and higher<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">USE MASTER;\nGO\n\nDROP DATABASE IF EXISTS MyDatabase;\nGO\n<\/pre>\n\n\n\n<p>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: <a href=\"https:\/\/expert-only.com\/en\/sql-server-db\/cannot-drop-the-database-because-it-is-currently-in-use\/\">Cannot drop the database because it is currently in use<\/a>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">SQL Server Administration tutorials<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/expert-only.com\/en\/sql-server-db\/install-a-second-instance-of-sql-server\/\">Tutorial to install a second instance of SQL Server.<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/dba\/create-a-sql-server-database-with-a-script\/\">How to create a SQL database with a script?<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/expert-only.com\/en\/sql-server-db\/empty-the-sql-server-transaction-log-and-fix-error-9002\/\">How to empty the MS SQL transaction log and fix the 9002 error?<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>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 class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/dba\/drop-database-script\/\" title=\"Drop a SQL Server database with a script\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":5961,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[488],"tags":[630,417],"class_list":{"0":"post-6640","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-dba","8":"tag-drop","9":"tag-file"},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6640","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=6640"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6640\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/5961"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=6640"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=6640"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=6640"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}