When you drop a database in SQL Server, the files are normally removed from the file system right away. POOF! Hope you had a backup if you ran DROP DATABASE against the wrong instance.
However, things are a little different if you take the database offline before you drop it. Consider the following code:
CREATE DATABASE DropMeOffline
( NAME = N'DropMeOffline',
FILENAME = N'S:\MSSQL\Data\DropMeOffline.mdf' ,
SIZE = 256MB ,
FILEGROWTH = 256MB )
( NAME = N'DropMeOffline_log',
FILENAME = N'S:\MSSQL\Data\DropMeOffline_log.ldf',
SIZE = 128MB,
FILEGROWTH = 128MB )
ALTER DATABASE DropMeOffline
SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE DropMeOffline;
Here’s what S:\MSSQL\Data looks like after I run the whole script, including DROP DATABASE…
This is by design and documented in Books Online — any database with one or more offline files won’t have the files removed from the filesystem when a database is dropped.
Why Take Databases Offline?
Taking databases offline is a very useful process tool. When you’re retiring or migrating a database off of a server, it’s often convenient to make the database unusable for a period before really deleting it. Because sometimes it turns out that a database is in use when we thought it wasn’t.
If you do choose to take databases offline before dropping them:
- Make sure you have enough copies of the last full backup for your policies
- Test that the backup restores properly elsewhere and isn’t corrupt
- When it comes time drop the database, remember that you’ll need to clean up the files manually if you drop it while it’s offline
But whatever you do, don’t skip that backup. The fact that the files aren’t automatically cleaned up doesn’t replace a good backup!
Brent Says: Wow, I didn’t know that.