Friday, March 11, 2005

Ooops - there goes my SQL Server database

I had a very bad moment today. Possibly in the world order of things not catastrophic, but enough to really spoil my day.

As a result of a series of coincidences that can never happen, can't be reproduced and may just occur again when I'm least expecting it my PowerBook suffered a hard-stop today. VirtualPC was open and merrily running a DTS Transfer of some tables in the SQL Instance. The world went grey and the PowerBook restarted....

Moments later I start up VPC and kick off Enterprise Manager and... there it is... the database with all the changes from the last couple of days (and yeah, I was about to back it up when the disaster struck) and right next to it the word suspect. Various assorted attempts to re-attach it failed (full chkdsk to make sure there was no damage and even creating a dummy empty log file to see if I could get it to verify... all failed) and I was resigned to trying to remember what stored procs I'd created and tables I'd added and changed (and there where a lot... it's early days on this particular project and development is at full steam ahead)

A quick Google on the subject to see if I've got any hope revealed a couple of woeful utilities that where no help what-so-ever and then I came across what turned out to be my saviour... MSSQLRecovery from OfficeRecovery wasn't very happy with the file at first - complaining of I/O errors, but replacing the log file with the earlier created empty one seemed to do the trick... and it started tip-toeing through the mess that was my database....

A few nail-biting hours later I was looking through a SQL script to recreate my tables and another to restore the carefully crafted Stored Procedures and the day was a lot better. There are a couple of curiosities in there - but it'll do me good to step through and verify why some things are how they are.

I'm often a little cynical about applications that offer to dig you out of the hell that is a corrupt file... but these guys seem to be as good as their word. If they can do this well for a SQL Server database, I can only imagine how good their Office, Outlook and other database recovery tools are. Hopefully I'll never have to find out ;)

No comments: