Restoring old MS SQL backups, no fun

Remember Microsoft SQL 2000? Remember it had DTS? And remember they were covertly stored in binary format? Really, I developed an allergy to binary storage since Java serialization abuse. Have you ever had to maintain code saving settings as serialized objects? No? Call yourself lucky. But back to the topic.

I was faced with what turned to be a small challenge: recover a self hacked tool which suddenly ceased sending its heartbeat emails. As any hacks, nobody knew who initially wrote it, where it used to live and generally what could have gone wrong. Did I mention enterprise environment? Well, after a small adventure, there was this old backup sitting in front of me, and it indeed included the missing DTS parts. Do you think you can get easily those DTS out of it? Nah, they are binary and you can see them only if the DB is restored as master database (MSDB called in MS SQL lingo). I’ve read about some script legends which supposedly extract these but didn’t invest much in finding them, because I thought I’d just restore the recovered MSDB in a one-time installation and export the DTS as VB scripts.

Ha. Naive me. The internet is full of people banging their heads about the error “cannot be restored because it was created by a different version of the server”. Mind you, we are not talking here about the version “MS SQL 2000” but about version numbers like 134218488, 134218546 or 134219767. So smart. It’s not only that Microsoft decided to make you dependent on cryptic version numbers, but they also decided to never document these numbers – in ANY place. Yes: you’re stuck because an undocumented version number. And do the people suffer about it… they need to figure out the version number of SP 3a with or without KB 8976234 and so on, yes, it’s REALLY that bad.

Solution? If you’re lucky, you can get it working in three steps:

  1. you install something remotely similar to the backup version (in my case MS SQL 2000 + SP3 was close enough)
  2. restore the old MSDB as another DB (let’s call it MSDB2)
  3. export the MSDB2
  4. restore the MSDB2 as MSDB
  5. cross your fingers the schema didn’t get changed by some KB inbetween and you can use your DB
  6. export the DTS as VB scripts
  7. grab some beer and celebrate

I was lucky, but it took me days until I found this solution buried in some forum answer… thank you Jane Matheson, whoever you are! Thank you thank you thank you!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s