Don't try this at home kids... (data recovery using DBCC PAGE)

  • Article

Time to cough up a real-life data recovery scenario to temporarily stop those people who badger me relentlessly for scenarios...

We have a situation currently with a customer who managed to delete all their data during a SAN reconfig and then found out that their tape backups hadn't been working properly (the same sad story I've heard a thousand times). They did have a primary filegroup backup plus a backup of a secondary filegroup from a different point. This is on SQL Server 2005.

The backups are restored, but the storage engine metadata for the table with the critical data in is messed up - sys.allocunits somehow has the wrong allocation unit IDs, first page, and root pages. So, what the hell can you do?

Easy (and fun if you're twisted like me).

We need to find the following info for the data allocation unit:

  • the page ID of the head of the IAM chain for the allocation unit
  • the root page of the clustered index
  • the first page of the clustered index

And there's a twist, the table has a column of type image so we also need to find the allocation unit ID and page ID of the head of the IAM chain for the LOB allocation unit.

The easiest way to do this is to scan through the data files, doing DBCC PAGEs of every page and storing the results in a table for later analysis. How? Ah, there's something I haven't told you about DBCC PAGE - it has a WITH TABLERESULTS option.

So, setup a simple script to INSERT/EXEC a DBCC PAGE of every page with option 0 (for speed, otherwise it has to take the time to go through and crack and format every record on every page) and save the results to a table.

To find the root page of the clustered index, query the table for the page with m_type = 2 (an index page) and the highest m_level. The level number increases from the leaf of the b-tree to the root page, so the page with the highest level is the root.

To find the first page, crack open the first record on the root page, and extract the page ID of the child page that the index record points to. This is the left-hand edge of the next level lower in the b-tree. Continue doing this until you find the page with m_level = 0. (Note that on SQL Server 2000, you'll need to look for m_level = 0 AND m_type = 1 - this is because pages at the leaf level and the level above in a SQL Server 2000 clustered index all have m_level = 0).

To find the head of the IAM chain is a little trickier. You need to find any page with m_type = 10 and following the m_prevPage links until its NULL. Then do an option 3 dump of the page and ensure that the sequenceNumber in the IAM page header is 0 (remember that an IAM chain is ordered by the sequence numbers in the IAM pages).

Cool - we've done the clustered index.

Now for the LOB allocation unit. Crack any row at the leaf level of the clustered index. You'll see that one of the columns is a text pointer, containing a page ID and slot number of the text fragment in the LOB allocation unit. Do a DBCC PAGE of that page and now you know the allocation unit ID of the LOB allocation unit and can use the methodology above to find the head of its IAM chain.

Now all you have to do is alter the hidden and unbindable sys.allocunits table to contain the correct data... Who's buying the beer? :-)