Dropping Offline Databases

…Oops.

“Oops” is not a word you want to utter often as a database administrator. Sure, accidents happen (who hasn’t missed that WHERE clause when deleting data) but there are steps you can take to minimize problems that will ruin your day, week, etc. Let’s look at a few ways to delete databases that supposedly are no longer needed.

Easy on the Drops

Have you thought about taking the database offline first? Just like when upgrading the SQL Server version but not yet changing the compatibility mode, you have a quick way to revert if problems occur. If someone calls in claiming the offline database is still needed, go into SSMS and bring it back online. This should be easier than tracking down the last backup, restoring, and doing any post restore work needed to make the database accessible. You’d also have to rely on your backups/restores…which you’re testing…right?

Clean up the Scraps

Did you know that there are extra steps you need to consider when dropping an offline database compared to dropping an online database? Let’s take a look at what those steps are below.

If you drop a database while it is online, the files will be removed. No additional work is needed to search out and remove files from the system. That’s not exactly the case when dropping an offline database.

I created a TestDB database for this example. Let’s take TestDB offline by right running:

ALTER DATABASE [TestDB] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

You could also right click on the database in SSMS, hover over Tasks, and select to Take Offline:

If we refresh Databases in SSMS, we will see an indication that the database is offline:

If we navigate to the path where our files reside, we’ll see that our TestDB files still exist in case we want to bring the database back online:

Back in SSMS, if we drop the database now:

USE master ;  
GO
DROP DATABASE TestDB;
GO

Our files are not removed and remain on the system:

It’s simple enough to delete the files from Windows Explorer since the files are not in use but remember to keep this in mind if you’re following this workflow.

Bite Your Tongue

Have you ever heard “oops” at an inopportune time? These situations would be some of the worst.

Thanks for reading!