5 Second Rule Doesn’t Apply to Dropped Databases or Does It?

5 Second Rule Doesn’t Apply to Dropped Databases or Does It?

Imagine you get a frantic call from someone, we’ll call him George, who just dropped a busy production database, and if he can’t get it back up ASAP, heads are going to roll (namely, his). The conversation might go something like this:

George: Oh crap, oh crap, oh crap! I’m going to get fired.

Me: What happened?

George: I dropped our busy production database by mistake. I thought I was on the test server.

Me: Do you have current backups?

George: Yes, but it has to be pulled from tape. It’s going to take at least 6 hours to restore from backup. I have to get it back up ASAP. Is there anyway to recover the dropped database?

Me: Maybe. How did you manage to drop a busy database? Dropping a database requires locking the database first.

George: I set it offline with rollback immediate first.

Me: Okay, good. We may be able to salvage this yet.

We had George’s production database back online in less than half an hour without restoring from backup. Intrigued? This is what we did.

Normally, when you drop a database, the database files are deleted as well. However, if the database is offline, the database files are not deleted. So even though the databae had been dropped, the files were still completely in tact in the file system. The one big question in my mind was whether or not the files would be attachable. In order to attach database files to SQL Server, the database has to be cleanly shut down first. Hopefully, setting the database offline shut it down cleanly.

Turns out that the database had been shut down cleanly. We found the database files and attached them to SQL. Nothing was lost (save for the active transactions that were rolled back when George set it offline.

Here some code to demonstrate

Use master;

-- Drop database if it exists
If DB_ID('Testdrop') Is Not Null
    Drop Database TestDrop;

-- Create a new database
Create Database TestDrop
    On (Name = N'TestDrop',
        FileName = N'C:\bak\TestDrop.mdf')
    Log On
        (Name = N'TestDrop_log',
        FileName = N'C:\bak\TestDrop_log.ldf');
Go

-- Add a table and some data
Use TestDrop;

Select *
Into dbo.AllDatabases
From sys.databases;

-- Set offline
Use master;

Alter Database TestDrop Set Offline With Rollback Immediate;
Go

-- Drop database
Drop Database TestDrop;
Go

-- Verify database files still exist
Exec xp_fileexist N'C:\bak\TestDrop.mdf';
Exec xp_fileexist N'C:\bak\TestDrop_log.ldf';
Go

-- Try to attach previously dropped files
Create Database TestDrop
    On (FileName = N'C:\bak\TestDrop.mdf')
    Log On
        (FileName = N'C:\bak\TestDrop_log.ldf')
 For Attach
 
 -- Validate data still exists
Select *
From TestDrop.dbo.AllDatabases;
Go

Disclaimer: Names were changed to protect the guilty.