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]
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.
I also find and mark the end offset too.
I copy this out and write it to the damaged file. Within the damaged file I go to the first offset.
I then paste/ insert it in as shown below.
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]
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.