How I completely remove a SQL Server database – Guaranteed to work – At least on my current setup

I don't know about you, but I always seem to fumble around when I need to remove a database. Specifically, I want to remove the underlying physical database file. Most SQL Server Databases are just two files: an *.mdf file and an *.ldf (log file).

The typical “Drop” command doesn’t do everything I want it to

I can afford to be very decisive on my dev computer. These steps are dangerous because your data is permanently gone, so proceed at your own risk about what you delete. Don’t try this at home – it is for professionals only.

So I have 5 sure-fire steps that are brutal, but get the job done

Step 1 - Figure out the folder of my physical database files (think .mdf and .ldf)

We will physically delete the database so we need to know where it lives. That is what this step is about.

This can be done by right mouse clicking on the database in SQL Server Management Studio (Process = SSMS) as follows:

Now find the physical database path. I recommend copying it to the clipboard. 

Step 2 - Get an administrator command prompt. Run my batch file to stop the
relevant SQL Server 2008 R2 services:

   net stop "SQL Server (LOCALHOST)"  

   net stop "SQL Server (MSSQLSERVER)"  

   net stop "SQL Server (SQLEXPRESS)"

Step 3 – Go to the physical location of the .mdf and .ldf files. Select "delete"

Step 4 - Restart SQL Server 2008 R2 services:

   net start "SQL Server (LOCALHOST)"  

   net start "SQL Server (MSSQLSERVER)"  

   net start "SQL Server (SQLEXPRESS)"

Step 5 – Go back to SQL Server Management Studio 2008 R2