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.

Advertisements




Enabling the Dedicated Administrator Connection in SQL Server

24 06 2011

SQL Server provides a special diagnostic connection for administrators when standard connections to the server are not possible. This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. The DAC also allows you to query the system base tables you don’t normally have access to. To connect using the DAC, prefix the instance name with “admin:”.

Here are the runes to enable it:

sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO