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.
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.