Corruption Challenge 4 - Alternate Solution

Here is the solution provided by Patrick Flynn. Congratulations Patrick for being one of this weeks winners.

I really liked this solution for a number of reasons, first it showed how to pull data from DBCC Page and actually convert it into rows and columns that could then be inserted back into a table. Secondly due to its use of Common Table Expressions.

This was done using SQL Server 2014.

Approach used was:

(a) Restore and check database – Corruption is related to Allocation Meta Data for dbo.Customers

(b) Use the non-clustered indexes on FirstName and LastName to recover data apart from Middle name – Total of 511740 rows in Table
Steps are in CorruptionChallange Setup.sql (requires disabling Database triggers

(c) Cannot directly select data from pages due to allocation issues but can select raw data vis DBCC Page (and DBCC IND)
Requires using DBCC Ind to get list of Data pages and DBCC Page to extract data
Steps to extract and Parse data are in DBCCPage_Extract

(d) After checking data was match by Id, FirstName and Last Name restore data into Customer table
This requires temporarily disabling FK on Orders table

(e) Check Database Corruption fixed

(f) Cleanup objects created and re-enable FK’s and Triggers
Steps are in CorruptionChallange_Cleanup.sql

Start by restoring the database. Of course Patrick was using different paths, so I had to modify the file paths to work on my test server.

Step 1 restore the database.

USE master;
GO

RESTORE DATABASE [CorruptionChallenge4]
   FROM DISK = N'C:\DBBackups\CorruptionChallenge4_Corrupt.bak'
   WITH FILE = 1,
        MOVE N'CorruptionChallenge4'
		  TO N'C:\SQL_DATA\CorruptionChallenge4.mdf',
        MOVE N'UserObjects'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf',
        MOVE N'CorruptionChallenge4_log'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
        NOUNLOAD,
		REPLACE,
		STATS = 5;
GO

Step2 DBCC CheckDB().
The next thing to try is DBCC CheckDB, however when I run this, it eventually crashes out and drops the connection to the SQL Server from SSMS.

USE [CorruptionChallenge4]
GO
DBCC CHECKDB('CorruptionChallenge4') WITH ALL_ERRORMSGS, NO_INFOMSGS

If DBCC CheckDB is run, and then cancelled after 6 seconds, it already has returned 78,000 lines of output ending with:
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 249. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 250. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 251. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 252. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 253. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 254. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 255. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 256. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 257. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 258. Column ‘subid’ was created NOT NULL, but is NULL in the row.
Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 29, index ID 1, partition ID 281474978611200, alloc unit ID 1900544 (type In-row data), page ID (3:192), row ID 259. Column ‘subid’ was created NOT NULL, but is NULL in the row.
CHECKDB found 0 allocation errors and 39344 consistency errors in table ‘sys.sysprivs’ (object ID 29).
CHECKDB found 0 allocation errors and 39344 consistency errors in database ‘CorruptionChallenge4’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionChallenge4).
Query was cancelled by user.

Now on to do something about that corruption.
Step 3 Check Current User Objects – Before == 5

 

SELECT Count(*)
FROM sys.objects
WHERE is_ms_shipped = 0;

Step 4 Errors are related to Clustered Index
Can use the Non clustered indexes to recover all data except MiddleName
Require to disable Database Triggers

DISABLE TRIGGER [noDropTables] ON DATABASE;
GO
DISABLE TRIGGER [noNewTables] ON DATABASE;
GO

CREATE TABLE [dbo].[Customers_Copy](
	[id] [int] NOT NULL,
	[FirstName] [varchar](30) NULL,
	[MiddleName] [varchar](30) NULL,
	[LastName] [varchar](30) NULL,
 CONSTRAINT [PK_Customers_1] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON) ON [UserObjects]
) ON [UserObjects]

GO

— GET what we can from the indexes.

INSERT INTO [dbo].[Customers_Copy]
     (id, FirstName, MiddleName, LastName)
SELECT a.id, a.FirstName, '' as MiddleName, b.LastName
FROM
(
	SELECT [id]
      ,[FirstName]
	FROM [CorruptionChallenge4].[dbo].[Customers]
	WITH (INDEX(ncCustomerFirstname))
) a
INNER JOIN
(
	SELECT [id]
      ,[LastName]
	FROM [CorruptionChallenge4].[dbo].[Customers]
        WITH (INDEX(ncCustomerLastname))
) b
ON a.id = b.id;

511740 rows recovered


SELECT *
FROM [dbo].[Customers_Copy];

Shows that we get back the data, but no middle names.

— Using DBCC Page data is intact in page but Allocation Data is incorrect
— Can extract data via DBCC Page and Parse Data to obtain MiddleName
— Create Table to store data from DBCC Page

Use CorruptionChallenge4
GO
CREATE TABLE [dbo].[Customers_Copy1](
	[id] [int] NOT NULL,
	[FirstName] [varchar](30) NULL,
	[MiddleName] [varchar](30) NULL,
	[LastName] [varchar](30) NULL,
 CONSTRAINT [PK_Customers_2] PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON) ON [UserObjects]
) ON [UserObjects];

Now create some temp tables, and use a cursor to fill them up using DBCC page.

DECLARE
	@DBName VARCHAR(256) = 'CorruptionChallenge4',
	@FileId INT = 3,
	@PageId INT,
	@DumpStyle int = 1;

DROP TABLE #DBCCIND;
CREATE TABLE #DBCCIND
(
  PageFID INT,
  PagePID INT,
  IAMFID INT,
  IAMPID INT,
  ObjectID INT,
  IndexID INT,
  PartitionNumber INT,
  PartitionID BIGINT,
  iam_chain_type VARCHAR(100),
  PageType INT,
  IndexLevel INT,
  NextPageFID INT,
  NextPagePID INT,
  PrevPageFID INT,
  PrevPagePID INT
);

/* create a temp table to hold dbcc page results */
DROP TABLE #DBCC_PAGE;
CREATE TABLE #DBCC_Page
(
parentObject varchar(200),
object varchar(200),
field varchar(200),
value varchar(200)
)
DROP TABLE #DBCC_Data;

CREATE TABLE #DBCC_Data
(
	PageId int,
	parentObject varchar(200),
	value varchar(100),
	I1 int,
	I2 int,
	I3 int
)

INSERT INTO #DBCCIND
  EXEC ('DBCC IND(''CorruptionChallenge4'', ''dbo.Customers'', 1)');

DECLARE @STR VARCHAR (2000) 

DECLARE db_cursor CURSOR FOR
SELECT PagePID
FROM #DBCCIND
WHERE PageType = 1 

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @PageId   

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @STR='DBCC PAGE (' + @DBName + ',' + Cast(@FileId as varchar(3)) + ','+
		Cast(@PageId as varchar(100)) + ','+
		Cast(@DumpStyle as varchar(100)) + ') with tableresults'

	/* store the output of dbcc page into the temp table */
	insert into #DBCC_Page exec(@STR) 

	INSERT INTO #DBCC_Data(PageId, parentObject, value, I1, I2, I3)
	Select
		@PageId as PageId,
		parentObject,
		value,
		CHARINDEX('Offset', parentObject, 1) as I1,
		CHARINDEX('Length', parentObject, 1) as I2,
		CHARINDEX('DumpStyle', parentObject, 1) as I3
	from #DBCC_Page
	WHERE object like 'Memory Dump %'

	DELETE FROM #DBCC_Page;

    FETCH NEXT FROM db_cursor INTO @PageId
END   

CLOSE db_cursor
DEALLOCATE db_cursor;

So now all the output from DBCC Page has been stored in the #DBCC_Page table. Let’s take a look at what’s there.

SELECT *
  FROM #DBCC_Data;

Now to translate the data and store it into a table called tmpCustomerData.

WITH Translate as
(
SELECT
	PageId,
	SUBSTRING(parentObject, 5, I1-7) as Slot,
	SUBSTRING(parentObject, I2+7, I3-I2-9) as Length,
	SUBSTRING(value,1,16) as LengthHex,
	SUBSTRING(value,20,45) as RawValues,
	SUBSTRING(value,67,20) as StringValues
FROM #DBCC_Data
), OrderedData as
(
	Select
		PageId,
		Slot,
		Length,
		LengthHex,
		RawValues,
		StringValues,
		ROW_NUMBER() Over (Partition By PageId,
Slot Order By LengthHex) as RowNum
	FROM Translate
)
SELECT
	a.PageId,
	a.Slot,
	a.Length,
	a.RawValues + ' ' + b.RawValues as HexValues,
	a.StringValues + b.StringValues as StringValues
INTO tmpCustomerData
FROM
(
	Select PageId, Slot, Length, RawValues, StringValues, RowNum
	from OrderedData
	WHERE RowNum =1
) a
INNER JOIN
(
	Select PageId, Slot, Length, RawValues, StringValues, RowNum
	from OrderedData
	WHERE RowNum = 2
) b
ON a.Slot = b.Slot
AND a.PageId  = b.PageId
ORDER BY a.PageId, a.Slot;
GO

Let’s take a look at the contents of the [tmpCustomerData] table.

SELECT *
  FROM tmpCustomerData;

The next step requires this function HexStrToVarBinary, so we will create it now.

CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
    DECLARE @hex char(1), @i int, @place bigint, @a bigint
    SET @i = LEN(@hexstr) 

    SET @place = convert(bigint,1)
    SET @a = convert(bigint, 0)

    WHILE (@i > 0 AND (SUBSTRING(@hexstr, @i, 1) like '[0-9A-Fa-f]'))
    BEGIN
        SET @hex = SUBSTRING(@hexstr, @i, 1)
        SET @a = @a +
    convert(bigint, CASE WHEN @hex LIKE '[0-9]'
         THEN CAST(@hex as int)
         ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
		SET @place = @place * convert(bigint,16)
        SET @i = @i - 1
    END
    RETURN convert(varbinary(8000),@a)
END;
GO

Then using our new function, and another Common Table Expression, the data is extracted and placed into a table called [Customer_Copy1].

WITH Data AS
(
	SELECT
	PageId, Slot, Length, HexValues, StringValues,
	CAST(dbo.HexStrToVarBinary('0x' + SUBSTRING(HexValues, 17, 2) + SUBSTRING(HexValues, 15,2) + SUBSTRING(HexValues, 13,2) + SUBSTRING(HexValues, 11,2)) AS INT) as id,
	CAST(dbo.HexStrToVarBinary('0x' + SUBSTRING(HexValues, 33, 2) + SUBSTRING(HexValues, 31,2)) AS INT) as V1,
	CAST(dbo.HexStrToVarBinary('0x' + SUBSTRING(HexValues, 38, 2) + SUBSTRING(HexValues, 35,2)) AS INT) as V2
	FROM   tmpCustomerData
)
INSERT INTO [dbo].[Customers_Copy1](id, FirstName, MiddleName, LastName)
SELECT Data.id,
	   SUBSTRING(StringValues,20, V1-19),
	   SUBSTRING(StringValues,V1+1, V2-V1),
	   SUBSTRING(StringValues,V2+1, 50)
  FROM Data;

Check the contents of the [Customers_Copy1] table.

SELECT *
  FROM Customers_Copy1;

Compare to data extracted from Non-Clustered, and they all match on the id.

SELECT Count(*)
FROM Customers_Copy1 c1
INNER JOIN Customers_Copy c2
	ON c1.id = c2.id;

— 511740 rows matching
Now to see how many names don’t match.

SELECT Count(*)
FROM Customers_Copy1 c1
INNER JOIN Customers_Copy c2
	ON c1.id = c2.id
WHERE c1.FirstName <> c2.FirstName
OR c2.LastName <> c2.LastName;

Time to clean up and put all the data back into the original table.
Cleanup Step1 Restore Data to original Table (need to disableFK)

USE [CorruptionChallenge4]
GO

ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Orders_People]
GO

SET IDENTITY_INSERT Customers ON

TRUNCATE TABLE [dbo].[Customers]

INSERT INTO [dbo].[Customers](id, FirstName, MiddleName, LastName)
SELECT
	id, FirstName, MiddleName, LastName
FROM [dbo].[Customers_Copy1];

SET IDENTITY_INSERT Customers OFF

Now confirm that the corruption is fixed.

DBCC CHECKDB('CorruptionChallenge4') WITH ALL_ERRORMSGS, NO_INFOMSGS

Put back everything we removed.

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_People] FOREIGN KEY([customerId])
REFERENCES [dbo].[Customers] ([id])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_People]
GO

-- (b) Drop Temporary objects
USE [CorruptionChallenge4];
GO

DROP TABLE [dbo].[Customers_Copy];
DROP TABLE [dbo].[Customers_Copy1];
DROP TABLE [dbo].[tmpCustomerData];
DROP FUNCTION [dbo].[HexStrToVarBinary];
GO

--(c) Restore Triggers
ENABLE TRIGGER [noDropTables] ON DATABASE;
ENABLE TRIGGER [noNewTables] ON DATABASE;

And finally the check needed to submit the challenge this week.

-- (d) Check Object Count restored to 5
SELECT Count(*)
FROM sys.objects
WHERE is_ms_shipped = 0;

-- (e) Check Data
Select * from dbo.Customers
WHERE id in (510900, 510901)

--510900	Steve	M	Stedman
--510901	William	V	STARK

And that’s how it’s done if you want to rip through the data pages with DBCC Page. Nice work Patrick.

Corruption Challenge 4 – Alternate Solution

Leave a Reply Cancel reply

Login

Corruption Challenge 4 – Alternate Solution

Related Posts

Why Having a SQL Assessment is Crucial for Ensuring Database Security

Maintaining Database Integrity with DBCC Checkdb Command

Leave a Reply Cancel reply

Login