Failover, Restore and Repair

I think one has been forgotten, and it's one that's not to be taken likely because you have to know your data. This method is Reconstruction.

Sometimes, in the event of only having an old backup, it is possible to reconstruct the data in certain circumstances. I had a table corruption s few years back where the only backup I could use for recovery was 3 weeks old. {edit} This also had the corrption so I had to go back to an earlier tape copy {end edit} You can usually guarantee that if something is going to go wrong, it will go wrong when you're on leave and no-one will pick it up, leaving it for you to deal with upon return.

The corruption was on the clustered index. I was unable to drop it as I kept getting errors. The clustered index ordered the data sequentially, so all the historic stuff was at the head of the table with the new data at the tail end. The corruption was somewhere in the middle (dated prior to the last good backup, good!)..

DBCC CHECKDB told me where the corruption was.

DBCC IND ('trapezedata', {tablename},-1)

provided me with a list of the PageIds for the corrupted table

I used these to fuel the next DBCC command...

DBCC TRACEon(3604)

DBCC PAGE ('{datanasename}',1,{PageID},3)

GO

provided me with the data on that page.

This allowed me to output all of the tail end data after the corrupted pages into a text file. The data output was in the format similar to :

Slot 54 Offset 0x10d4

---------------------

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

484730D4: 003e0030 000a5d59 00000001 00000000 0.>.Y]..........

484730E4: 00000000 00000000 00000000 00000000 ................

484730F4: 0000002b 00000000 00000000 00000000 +...............

48473104: 00000000 00000000 00000000 00120001 ................

48473114: 02000000 4e004c00 494c4300 5453 .....L.N.CLIST

LegId = 679257

PassType = CLI

SpaceType = ST

NumSpacesPu = 1

NumSpacesDo = 0

FareTypeId = 0

FareAmount = 0

FundingSource1Amount = 0

FundingSource2Amount = 0

FundingSource3Amount = 0

FundingSourceId1 = 43

FundingSourceId2 = 0

FundingSourceId3 = 0

FundingSource4Amount = 0

FundingSource5Amount = 0

FundingSourceId4 = 0

FundingSourceId5 = 0

FareCalcType = 1

I ran the DBCC PAGE for the newer pages/data that did not appear on the restored database , which I had restored to a copy table. I loaded the resulting (and large) text file back into a temporary table, cleaned up the data to leave the column names and values and then transposed these back into columns with a little bit of TSQL scripting.

I was then able to insert the latest data back into the restored table, drop the corrupted one and rename the restored one to replace it.

No data loss, but there was some loss of functionality to the users system during the fix.

It's a horrible position to be in and not an elegant or easy fix, but it can be done this way if you run out of ideas.