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 😉