SQL Server Recovering Data via a Hex Editor

I got bored (really bored) one weekend I decided to challenge myself.  I had corruption within a specific table (localised within a page) with no backups handy only an old image of the data files. The challenge being salvage data without attaching the data files.

So in the dodgy database when I execute a select statement I would get something like:

SELECT * from
[PartyWorks].[Person].[AddressType]

Msg 824, Level 24, State 2, Line 2 error occurred during a read of page (1:880) in database ID 21 Etc

I know this is on page 880 so I decided to look at it.

DBCC TRACEON (3604)
DBCC PAGE (N'PartyWorks',1,880,3);

From looking at the output I work out start and end offsets from the starting point for page 880 using basic addition from the top slot to the bottom, something like:

SELECT (8192 * 880) AS [Starting point]

SELECT CONVERT (INT, 0x60 +4) as [start offset decimal]

SELECT CONVERT (INT, 0x16d +0) as [END offset decimal]

offsets.JPG

So I am interested in start point = 7209060 and end point = 7209425 when using decimal points which you need when in a hex editor. So within the good file I find these points.

decimalpoints.JPG

I also find and mark the end offset too.

end.JPG

I copy this out and write it to the damaged file. Within the damaged file I go to the first offset.

startpoint.JPG

I then paste/ insert it in as shown below.

pasteit.JPG

I then save this MDF file (the dodgy one) and bring the database online and issue a SELECT statement.

ALTER DATABASE [PartyWorks] SET ONLINE
GO

SELECT * from
[PartyWorks].[Person].[AddressType]

selecttable.JPG

Well no more “msg 824, Level 24, State 2, Line 2” messages and I can see data. (Better than nothing)

Something I would never do in real life because let’s be honest, it’s quite “dirty” but against my own installation I fancied giving it a go. Maybe I need to get out more and go to the cinema or something.

I might buy a puppy.

puppy.JPG

2 thoughts on “SQL Server Recovering Data via a Hex Editor

  1. You’ve just prompted me to add a new category to the topics in my Evernote repository. “Don’t Do This At Home!” Thanks for a great article.

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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