Recovering data from SQL Server backups

30 08 2011

When disaster strikes your backup process what can you do to recover? Recently a customer called in dire straits. They had not noticed that over the last month their backups had been failing due to a lack of disk space. Left with a couple of random, incomplete full backups and an orphaned differential, they needed some help to recover their invaluable data.

So what can you do? Well if you understand the structure of a SQL Server backup you can get in there and get that data. A SQL Server backup file stores the pages of the data file in Microsoft Tape Format with some SQL Server extensions. That means that somewhere in the incomplete backups you have there are data pages with data rows waiting to be extracted.

There are a couple of approaches that can work. If you are lucky then the object id of the table you are interested in has not changed in recent times. You can walk through every 8kb data page looking for those that apply to that object id and then extract the data rows from those pages.

If you are unlucky the object id has changed and you have no idea what it was, in that case you must rely on the schema to help you out. Each data row contains a little information about itself i.e. the number of columns it contains and the length of the fixed length columns. By going through each row and testing if it has the same number of columns and same fixed length size as the schema of the table you are interested in you can guess that this row belongs to the table you are interested in and extract it. This approach worked in our customers case but there is always a chance that you will have false positives when two tables have the same schema.

I’ve started a github project to create a tool to help in this sort of situation. Check it out here.




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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: