Wednesday, November 4, 2009

DBCC to the Rescue!

It's rare when I find myself in a situation like this. One of the hard drives (E) died on one of the servers that housed the log files for some databases. So log files are gone but the database (mdf) files are still intact. No big deal, we'll just recover everything from backup. Right.

Well, there was no backup done for this server. At all.

I know! I know! What am I doing as a DBA as that's the first thing a DBA makes sure of is to have backups of everything. It happened because our server admin neglected to tell me that they had SQL Express installed on this box and not only that but what he has originally intended only as a dev box for the Office Communicator backend has been promoted to live a while back!

I have tried a few tricks to get the database back online but nothing worked. In fact I made matters worse because somehow I managed to make the database dissapear (a.k.a. detach) which was not my intention.

Eventually I got the database to attach by doing the following:
  • I created a blank database with the same name.
  • I shut down SQL Server.
  • I replaced the blank database file with the original database file.
  • I deleted the blank log file.
  • I restarted SQl Server.

So the database appeared in the list but it was in a recovery mode so still useless and I was back on square one.

I thought that's it, we lost everything... until I found this page from SQLSkills. I would like to give full credit to Paul S. Randall for this solution. His page explains everything about how to use DBCC to have SQL Server rebuild the log file in emergency mode. I will just post the actual script to use. If you need to use this script, replace DB_in_distress with the name of your own database.

--put the database in emergency mode
ALTER DATABASE DB_in_distress SET EMERGENCY;
go
--set it to single user mode
ALTER DATABASE DB_in_distress SET SINGLE_USER;
go
--rebuild log
DBCC CHECKDB (DB_in_distress, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
go
--check to make sure DB is back online
SELECT state_desc FROM sys.databases WHERE name='DB_in_distress'
--put it back into multi-user mode
ALTER DATABASE DB_in_distress SET MULTI_USER

I'd like to mention one important point. SQL Server insisted on using the original location to recreate the log. Therefore this solution did not work for us until we recreated the E drive that caused all this problem.

Obviously, as Mr. Randall mentions, this should be used only as the very, very last resort and you can expect some data loss. However, some data loss is still better then all data lost.