Database Corruption Challenge Week 7 - Alternate Solution

The alternate solution to the Database Corruption Challenge this week was created by Patrick Flynn. This solution is the only solution to successfully recover all the data without using any of the backups. If the challenge had been structured differently and all you had was just the database files and no backups, this solution would work just fine.

As you read through this solution, you will notice the use of Common Table Expressions throughout the solution. Patrick is the first person that I have seen write TSQL code that uses Common Table Expressions more than I do. (I mean that as a compliment Patrick)

To start with let’s add the UDF_Convert_Hex_to_Binary function into the master database to match what Patrick had in his system.

All code provided by Patrick, comments and screenshots by me.

-- Step 0 - Create Supporting function
USE [master]
GO
CREATE FUNCTION [dbo].[UDF_Convert_Hex_to_Binary]
(
      @HEX VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN

	DECLARE @BINARY VARCHAR(MAX)

	;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
	N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
	N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
	N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
	FROM N3 AS X, N3 AS Y)

	SELECT @BINARY=ISNULL(@BINARY,'')
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 128) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 64) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 32) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 16) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 8) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 4) % 2)
	+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 2) % 2)
	+ CONVERT(NVARCHAR(1), SUBSTRING(@HEX, Nums.n, 1) % 2) 

	FROM N4 Nums
	WHERE Nums.n<=LEN(@HEX) 

	RETURN @BINARY
END
GO

Once that function is created, let’s drop the previous CorruptionChallenge7 database, because this is probably not our first time running through the solution. If you have a different way of checking for a database and dropping it, please see my blog post related to this topic earlier today.


-- Step 1 Create database using supplied files
USE master;
GO
IF DB_ID ('CorruptionChallenge7') IS NOT NULL
BEGIN
     ALTER DATABASE CorruptionChallenge7 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	 DROP DATABASE CorruptionChallenge7
END

Now we will use the MDF, LDF, and NDF files provided in the solution, and attach them as the CorruptionChallenge7 database.


-- Attached Supplied Files (Will upgrade database to current version)
CREATE DATABASE [CorruptionChallenge7] ON
( FILENAME = N'C:\SQL_DATA\CorruptionChallenge7.mdf' ),
( FILENAME = N'C:\SQL_DATA\CorruptionChallenge7_log.ldf' ),
( FILENAME = N'C:\SQL_DATA\UserObjects.ndf' )
 FOR ATTACH
GO

Now we have a database that we are ready to work on figuring out where the rows in the [Record1000] table went. So let’s see what we have in the current database log that hasn’t yet been backed up.


-- Step 2 Extract and Parse data from log file
Use CorruptionChallenge7
GO

Declare @TransactionID varchar(30);
Declare @AllocUnitId BigInt;

Select @TransactionID = TransactionID from
(
	SELECT  TOP 1
		[TRANSACTION ID] as TransactionID,
		[Begin Time] as BeginTime
	FROM sys.fn_dblog(NULL, NULL)
	WHERE Context IN ('LCX_NULL')
	AND Operation in ('LOP_BEGIN_XACT')
	And [Transaction Name] In ('DELETE')
	ORDER BY [Begin Time] Desc
) a;

CREATE TABLE #RowData(
	[RowID] INT IDENTITY(1,1),
	[RowLogContents] [varbinary](8000) NULL,
	[AllocUnitID] [bigint] NULL,
	[TransactionID] [varchar](14) NOT NULL,
	[SlotId] [int] NULL,
	[FixedLengthData] [smallint] NULL,
	[TotalNoOfCols] [smallint],
	[NullBitMapLength] [smallint],
	[NullBytes]         VARBINARY(8000),
	[TotalNoofVarCols] [smallint],
	[ColumnOffsetArray] VARBINARY(8000),
	[VarColumnStart] [smallint],
	[NullBitMap] varchar(128)
); 

WITH RowData(RowLogContents, AllocUnitID, TransactionID, SlotId, FixedLengthData) AS
(
	SELECT
		 [RowLog Contents 0]
		,[AllocUnitID]
		,[Transaction ID]
		,[Slot ID]
		,CONVERT(SMALLINT,CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 3, 2))))
	FROM sys.fn_dblog(NULL, NULL)
	WHERE SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
	AND [Transaction ID] = @TransactionID
)
INSERT INTO #RowData(RowLogContents, AllocUnitID, TransactionID, SlotId, FixedLengthData)
Select
	RowLogContents
	,AllocUnitID
	,TransactionID
	,SlotId
	,FixedLengthData
from RowData; 

SELECT * FROM #RowData;

Now there is a lot there to sort out.

Now to calculate the number of columns in each row deleted.

Update #RowData
Set TotalNoOfCols =
	CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(RowLogContents, FixedLengthData + 1, 2))))
from #RowData

SELECT * FROM #RowData;

Now that the number of columns has been calculated we calculate the NullBitMapLength. All of these will be used later to extract the actual rows from the log data.

Update #RowData
Set NullBitMapLength = -- [NullBitMapLength]=ceiling([Total No of Columns] /8.0)
	CONVERT(INT, ceiling(TotalNoOfCols/8.0))
from #RowData;

SELECT * FROM #RowData;

Next the NullBytes and TotalNoofVarColumsn fields are calculated and filled in.

Update #RowData
Set NullBytes = --[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
	SUBSTRING(RowLogContents, FixedLengthData + 3, NullBitMapLength),
	TotalNoofVarCols = --[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
	CASE
		WHEN SUBSTRING(RowLogContents, 1, 1) In (0x10,0x30,0x70)
			THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(RowLogContents, FixedLengthData + 3 + NullBitMapLength, 2))))
		ELSE null
	END
from #RowData;

SELECT * FROM #RowData;

Three more fields to calculate, the ColumnOffsetArray, the VarColumnStart, and the NullBitmap.

Update #RowData
Set ColumnOffsetArray = --[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
	CASE
		WHEN SUBSTRING(RowLogContents, 1, 1) In (0x10,0x30,0x70)
		THEN SUBSTRING(RowLogContents, FixedLengthData + 3 + NullBitMapLength + 2,
			(CASE
				WHEN SUBSTRING(RowLogContents, 1, 1) In (0x10,0x30,0x70)
				THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(RowLogContents, FixedLengthData + 3 + NullBitMapLength, 2))))
				ELSE null
			 END) * 2)
		ELSE null
	END,
	VarColumnStart = --  Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
	CASE
		WHEN SUBSTRING(RowLogContents, 1, 1)In (0x10,0x30,0x70)
		THEN  (FixedLengthData + 4 + NullBitMapLength
		+ ((CASE WHEN SUBSTRING(RowLogContents, 1, 1) In (0x10,0x30,0x70)
				 THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING(RowLogContents, FixedLengthData + 3 + NullBitMapLength, 2))))
				 ELSE null
			END) * 2))
		ELSE null
	End,
	NullBitMap = master.dbo.UDF_Convert_Hex_to_Binary(NullBytes)
from #RowData;

SELECT * FROM #RowData;

Now we need to figure out the columns for this table.

/*
Parse Table
*/

CREATE TABLE #ColumnNameAndData
(
 [RowID]           int,
 [Rowlogcontents]   varbinary(Max),
 [ColumnName]       sysname,
 [nullbit]          smallint,
 [leaf_offset]      smallint,
 [length]           smallint,
 [system_type_id]   tinyint,
 [bitpos]           tinyint,
 [xprec]            tinyint,
 [xscale]           tinyint,
 [is_null]          int,
 [ColumnValueSize]	int,
 [ColumnLength]		int,
 [hex_Value]        varbinary(max),
 [SlotID]          int,
 [Updated]           int
);

--Create common table expression and join it with the rowdata table
-- to get each column details
-- varlength data
With ColumnNameInfo(RowID, Rowlogcontents, ColumnName, nullbit, leaf_offset, length, system_type_id, bitpos, xprec, xscale, is_null, VarColOffsetEnd, VarColOffsetStart, VarColumnStart, SlotId) as
(
	SELECT
		[RowID],
		Rowlogcontents,
		[NAME] ,
		cols.leaf_null_bit AS nullbit,
		leaf_offset,
		ISNULL(syscolumns.length, cols.max_length) AS [length],
		cols.system_type_id,
		cols.leaf_bit_position AS bitpos,
		ISNULL(syscolumns.xprec, cols.precision) AS xprec,
		ISNULL(syscolumns.xscale, cols.scale) AS xscale,
		SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
		CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) as VarColOffsetEnd,
		CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))) as VarColOffsetStart,
		VarColumnStart,
		SlotId
	FROM #RowData A
	Inner Join sys.allocation_units allocunits
		On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
	INNER JOIN sys.partitions partitions
		ON (allocunits.type IN (1, 3) AND partitions.hobt_id = allocunits.container_id)
		OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
	INNER JOIN sys.system_internals_partition_columns cols
		ON cols.partition_id = partitions.partition_id
	LEFT OUTER JOIN syscolumns
		ON syscolumns.id = partitions.object_id
		AND syscolumns.colid = cols.partition_column_id
	WHERE leaf_offset<0
), VarLengthData as
(
	Select
	c.*,
	[Column value Size] =
 		(CASE
			WHEN leaf_offset<1 and is_null=0
			THEN
				(Case
					When    VarColOffsetEnd > 30000
					THEN	VarColOffsetEnd - POWER(2, 15)
					ELSE    VarColOffsetEnd
				END)
		 END),
	VarColOffsetStartAdj = ISNULL(NULLIF(VarColOffsetStart, 0), [VarColumnStart])
	from ColumnNameInfo c
), HexDataInfo as
(
	Select vd.*,
	 [Column Length] =
	 (CASE
		WHEN leaf_offset<1 and is_null=0
		THEN
			(Case
				When VarColOffsetEnd > 30000 And VarColOffsetStartAdj < 30000
				THEN
					(Case
						When [system_type_id] In (35,34,99)
							Then 16
							else 24
					 end)
		When VarColOffsetEnd >30000 And VarColOffsetStartAdj>30000
			THEN
			(Case
				When [system_type_id] In (35,34,99)
					Then 16
					else 24
			 end) --24
		When VarColOffsetEnd <30000 And VarColOffsetStartAdj<30000
			THEN (VarColOffsetEnd - VarColOffsetStartAdj)
		When VarColOffsetEnd <30000 And VarColOffsetStartAdj>30000
			THEN POWER(2, 15) +VarColOffsetEnd - VarColOffsetStartAdj
		END)
	 END),
	[VarColStart] =
	(Case
		When VarColOffsetEnd > 30000
			THEN VarColOffsetEnd - POWER(2, 15)
		ELSE
			VarColOffsetEnd
	END) -
    (Case
		When VarColOffsetEnd > 30000 And VarColOffsetStartAdj < 30000
			THEN  (Case When [system_type_id] In (35,34,99) Then 16 else 24  end)
		When VarColOffsetEnd > 30000 And VarColOffsetStartAdj>30000
			THEN  (Case When [system_type_id] In (35,34,99) Then 16 else 24  end)
		When VarColOffsetEnd < 30000 And VarColOffsetStartAdj < 30000
			THEN VarColOffsetEnd - VarColOffsetStartAdj
		When VarColOffsetEnd < 30000 And VarColOffsetStartAdj > 30000
			THEN POWER(2, 15) +VarColOffsetEnd - VarColOffsetStartAdj
	END) + 1,
	VarDataLength =
	(Case
		When VarColOffsetEnd > 30000 And VarColOffsetStartAdj < 30000
			THEN (Case When [system_type_id] In (35,34,99) Then 16 else 24  end)
		When VarColOffsetEnd > 30000 And VarColOffsetStartAdj > 30000
			THEN (Case When [system_type_id] In (35,34,99) Then 16 else 24  end)
		When VarColOffsetEnd < 30000 And VarColOffsetStartAdj < 30000
			THEN ABS(VarColOffsetEnd - VarColOffsetStartAdj)
		When VarColOffsetEnd < 30000 And VarColOffsetStartAdj > 30000
			THEN POWER(2, 15) + VarColOffsetEnd - VarColOffsetStartAdj
	END)
	FROM VarLengthData vd
)
INSERT INTO #ColumnNameAndData(RowID, Rowlogcontents, ColumnName, nullbit, leaf_offset, [length],system_type_id, bitpos, xprec, xscale, is_null, ColumnValueSize, ColumnLength, hex_Value, SlotID, Updated)
Select hd.RowID, hd.Rowlogcontents, hd.ColumnName, hd.nullbit, hd.leaf_offset, hd.[length], hd.system_type_id, hd.bitpos, hd.xprec, hd.xscale, hd.is_null, hd.[Column value Size], hd.[Column Length],
	hex_Value =
	(CASE
		WHEN is_null=1 AND VarDataLength = 0 THEN NULL
		ELSE
			SUBSTRING(Rowlogcontents, VarColStart, VarDataLength)
	END),
	hd.SlotId,
	0 as Updated
FROM HexDataInfo hd;

SELECT * FROM #ColumnNameAndData;

Now to add some more column information.

INSERT INTO #ColumnNameAndData(RowID, Rowlogcontents, ColumnName, nullbit, leaf_offset, [length],system_type_id, bitpos, xprec, xscale, is_null, ColumnValueSize, ColumnLength, hex_Value, SlotID, Updated)
SELECT
	[RowID],
	Rowlogcontents,
	[NAME] ,
	cols.leaf_null_bit AS nullbit,
	leaf_offset,
	ISNULL(syscolumns.length, cols.max_length) AS [length],
	cols.system_type_id,
	cols.leaf_bit_position AS bitpos,
	ISNULL(syscolumns.xprec, cols.precision) AS xprec,
	ISNULL(syscolumns.xscale, cols.scale) AS xscale,
	SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
	(
	 SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0)
	 FROM sys.system_internals_partition_columns C
	 WHERE cols.partition_id =C.partition_id
	 And C.leaf_null_bit<cols.leaf_null_bit
	 )+5 AS [Column value Size],
	syscolumns.length AS [Column Length]
	,CASE
		WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) =1
			THEN NULL
		ELSE
			SUBSTRING(Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 And C.leaf_bit_position=0 THEN max_length ELSE 0 END),0) FROM
			sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5
			,syscolumns.length) END AS hex_Value
	,[SlotID]
	,0
FROM #RowData A
Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset>0;

SELECT * FROM #ColumnNameAndData;

Next we create a place to store the recovered data and fill it in.

CREATE TABLE [#RecoverdData]
(
    [ColumnName]  VARCHAR(MAX),
	[ColOrder] SMALLINT,
    [FieldValue] NVARCHAR(MAX),
    [Rowlogcontents] VARBINARY(8000),
    [RowID] int
)

INSERT INTO #RecoverdData(ColumnName, ColOrder, FieldValue, Rowlogcontents, RowID)
SELECT ColumnName,
nullbit,
CASE
 WHEN system_type_id IN (231, 239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))  --NVARCHAR ,NCHAR
 WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value)))  --VARCHAR,CHAR
 WHEN system_type_id IN (35) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) --Text
 WHEN system_type_id IN (99) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --nText
 WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER
 WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER
 WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER
 WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER
 WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME
 WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME
 WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC
 WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38,20), CONVERT(VARBINARY,Convert(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- DECIMAL
 WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY
 WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))  -- BIT
 WHEN system_type_id =62 THEN  RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT
 When system_type_id =59 THEN  Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real
 WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY
 WHEN system_type_id =34 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)')  --IMAGE
 WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
 WHEN system_type_id =231 THEN CONVERT(VARCHAR(MAX),CONVERT(sysname,hex_Value)) --SYSNAME
 WHEN system_type_id =241 THEN CONVERT(VARCHAR(MAX),CONVERT(xml,hex_Value)) --XML

 WHEN system_type_id =189 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') --TIMESTAMP
 WHEN system_type_id=98 THEN (CASE
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(Substring(hex_Value,3,Len(hex_Value))))))  -- INTEGER
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=108 THEN CONVERT(VARCHAR(MAX),CONVERT(numeric(38,20),CONVERT(VARBINARY(1),Substring(hex_Value,3,1)) +CONVERT(VARBINARY(1),Substring(hex_Value,4,1))+CONVERT(VARBINARY(1),0) + Substring(hex_Value,5,Len(hex_Value)))) --- NUMERIC
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=167 THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),Substring(hex_Value,9,Len(hex_Value))))) --VARCHAR,CHAR
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,Substring((hex_Value),3,20))) --UNIQUEIDENTIFIER
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=61 THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (Substring(hex_Value,3,LEN(hex_Value)) ))),100) --DATETIME
 WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=165 THEN '0x'+ SUBSTRING((CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)'),11,LEN(hex_Value)) -- BINARY,VARBINARY
 END)
END AS FieldValue
,[Rowlogcontents]
,[RowID]
FROM #ColumnNameAndData
ORDER BY RowID, nullbit

SELECT * FROM #RecoverdData;

Now let’s test the query to pull data out of the #RecoverdData table and PIVOT it to something we can use.

SELECT [Record1000Id],[FireDeptID],[FireDeptState],[AlarmDate],[IncidentNumber],[ExposureNumberZeroBased],[RecordType],[TransactionType],[FireDepartmentStation]
FROM (
	Select
		ColumnName,
		FieldValue,
		RowId
    FROM #RecoverdData
	) a
PIVOT (Min([FieldValue]) FOR ColumnName IN ([Record1000Id],[FireDeptID],[FireDeptState],[AlarmDate],[IncidentNumber],[ExposureNumberZeroBased],[RecordType],[TransactionType],[FireDepartmentStation])) AS pvt

Now its starting to look like something we can use.

Since the data looks good, we now put it back into the table it was originally deleted from.

-- Step 3 Insert data back into table
-- Insert into table
SET IDENTITY_INSERT [OPEN_NFIRS].[Record1000] ON;
GO

INSERT into [OPEN_NFIRS].[Record1000]([Record1000Id],[FireDeptID],[FireDeptState],[AlarmDate],[IncidentNumber],[ExposureNumberZeroBased],[RecordType],[TransactionType],[FireDepartmentStation])
SELECT [Record1000Id],[FireDeptID],[FireDeptState],[AlarmDate],[IncidentNumber],[ExposureNumberZeroBased],[RecordType],[TransactionType],[FireDepartmentStation]
FROM (
	Select
		ColumnName,
		FieldValue,
		RowId
    FROM #RecoverdData
	) a
PIVOT (Min([FieldValue]) FOR ColumnName IN ([Record1000Id],[FireDeptID],[FireDeptState],[AlarmDate],[IncidentNumber],[ExposureNumberZeroBased],[RecordType],[TransactionType],[FireDepartmentStation])) AS pvt 

SET IDENTITY_INSERT [OPEN_NFIRS].[Record1000] OFF;

Data restored, and all is good, now we run the checksum query to confirm, and … we get the right results.

SELECT COUNT([Record1000Id]) as check1
      ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2
      ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3
      ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4
      ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5
      ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6
      ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7
      ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8
      ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9
FROM [OPEN_NFIRS].[Record1000];

Clean things up.

-- Step 4 Cleanup
Drop Table #RowData
Drop Table #ColumnNameAndData
Drop Table #RecoverdData

And there you have it, recovering 3010 delete rows without going to a backup. In fact this solution, assuming the database already existed and didn’t need to be attached, could be done with no downtime, and without bumping any users out of the system.

You can download the PatrickFlynn2NoRestore.zip.

Nice solution Patrick!

2 Comments on “Database Corruption Challenge Week 7 – Alternate Solution”

Patrick Flynn says:

June 8, 2015 at 16:23

Hi Steve.

I would like to add a clarification to the post.

Most of this solution is based around code and posts done by Muhammad Imran (raresql.com)
In particular: http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

The credit for developing this code belongs with him.

My main efforts were to
(a) Modify the code for the particular situation
(b) Split out much of the repeated calculations in order to make the code more readable and understandable

paSQuaLe ceglie says:

June 9, 2015 at 07:05

Really great solution. This week there are a lot of things to learn … thanks Steve for challenge and thanks Patrick for sharing your solution … ad finally thanks to Muhammad Imran too

enjoy your SQL

paSQuaLe

Database Corruption Challenge Week 7 – Alternate Solution

2 Comments on “Database Corruption Challenge Week 7 – Alternate Solution”

Leave a Reply Cancel reply

Login