Week 5 – Winning Solution – Database Corruption Challenge

Find out more about the Week 5 Challenge on the overview blog post.

The winning solution, was submitted by Patrick Flynn, just 3 hours and 18 minutes after the challenge was posted. Patrick provided me with the following steps, and TSQL code to back up the steps:

The process was

(a)    Restore two copies from backup (Old backup has 400 Customers and 1622 Orders)

(b)    Use the supplied MDF, LDF and NDF to hack attach to instance

(c)     Try using tail-log backup but log chain broken also as errors in Boot page (1:9) unable to use all files

(d)    Instead using original backup replace only log file and ndf file (containing user data)

(e)    After switching to emergency mode found errors in Orders table (1:175) but this does not contain new rows

(f)     Able to select the newest 181 rows and insert into original orders table

Final Status was

Orders = 12803 rows

Customers = 400 rows

No corruption remaining

Here is the exact TSQL code that supported that solution.

To start with he provided me with one of the things that was needed to win the solution, the exact version of SQL Server being used. Note everything below in the SQL code was written by Patrick, the commentary in between the code was written by me.

SELECT @@VERSION;
/*
Microsoft SQL Server 2014 - 12.0.2456.0 (X64)
Dec 11 2014 17:32:03
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
*/

Step 1 Restore Backup as Copy, notice the “\Backup\” in the path to the files, and the “_Original” in the name of the database.  After doing the restore, Patrick checked the count of rows in the two tables, and noted them for future reference.

-- Step 1 Restore Backup as Copy

USE [master]
RESTORE DATABASE [CorruptionChallenge5_Original]
FROM DISK = N'C:\DBBackups\CorruptionChallenge5_FullBackup1.bak'
WITH FILE = 1,
MOVE N'CorruptionChallenge5' TO N'C:\SQL_Data\Backup\CorruptionChallenge5.mdf',
MOVE N'UserObjects' TO N'C:\SQL_Data\Backup\CorruptionChallenge5_UserObjects.ndf',
MOVE N'CorruptionChallenge5_log' TO N'C:\SQL_Data\Backup\CorruptionChallenge5_log.ldf',
REPLACE, STATS = 5
GO
/*
Customers table = 400 Records
Orders Table = 12622 Records
*/

Then another restore of the same backup, this time to a different directory than the first with database name being [CorruptionChallenge5]. The file names are the same, just in a different directory.

-- Step 2 Restore Backup as Database
USE [master]
RESTORE DATABASE [CorruptionChallenge5]
FROM DISK = N'C:\DBBackups\CorruptionChallenge5_FullBackup1.bak'
WITH FILE = 1,
MOVE N'CorruptionChallenge5' TO N'C:\SQL_Data\CorruptionChallenge5.mdf',
MOVE N'UserObjects' TO N'C:\SQL_Data\CorruptionChallenge5_UserObjects.ndf',
MOVE N'CorruptionChallenge5_log' TO N'C:\SQL_Data\CorruptionChallenge5_log.ldf',
REPLACE, STATS = 5
GO

Next steps 3, 4 and 5.  This is what is typically referred to as a Hack Attach, a way to connect to a database that is corrupt by just switching the files out. The database needs to be offline to do this, otherwise you can’t overwrite the files.  Note here, only the ndf file and the log are being replaced.  Since all of the user objects where in the CorruptionChallenge5_UserObjects.ndf file, we were able to get past the boot page error.

-- Step 3
ALTER Database CorruptionChallenge5 SET OFFLINE WITH ROLLBACK IMMEDIATE

-- Step 4
/*
Replace the Log File and UserObjects File with
CorruptionChallenge5_UserObjects.ndf and CorruptionChallenge5_log.ldf
*/

-- Step 5 Bring Online
ALTER Database CorruptionChallenge5 SET ONLINE
-- Comes up as suspect

Now jump into Emergency Mode.

-- Step 6 Set to Emergency Mode
ALTER Database CorruptionChallenge5 SET EMERGENCY

Run CheckDB to see whats going on. It appears that there are some problems.

-- Step 7
DBCC CHECKDB('CorruptionChallenge5') WITH NO_INFOMSGS, ALL_ERRORMSGS
-- Errors found in Orders table
/*
Msg 8944, Level 16, State 16, Line 39
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:175), row 78. Test (VarColOffsets + (int)(sizeof (VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 8221 and 102.
Msg 8944, Level 16, State 16, Line 39
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data), page (3:175), row 78. Test (VarColOffsets + (int)(sizeof (VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 8221 and 102.
Msg 8928, Level 16, State 1, Line 39
Object ID 2105058535, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data): Page (3:175) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 39
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:175) was not seen in the scan although its parent (3:14) and previous (3:174) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 39
Table error: Object ID 2105058535, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594044350464 (type In-row data). Page (3:176) is missing a reference from previous page (3:175). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'Orders' (object ID 2105058535).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'CorruptionChallenge5'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionChallenge5)
*/

Lets find out more about the difference between the last backup and the current.

-- Step 8
-- Compare Allocation between last backup and current
DBCC IND('CorruptionChallenge5', 'dbo.Orders', 1) -- 165 Rows Last Page 179
DBCC IND('CorruptionChallenge5_Original', 'dbo.Orders', 1) -- 163 Rows Last Page 177

New data added.

-- Step 9
-- New data has been added to Orders but error on already existing page
-- Find Max(Id) from Backup Copy
Select Max(id)
FROM [CorruptionChallenge5_Original].[dbo].[Orders] -- -2147471026

See what we can get out of the bad file.

-- Step 10 Try selecting New data from damaged file
Select *
FROM [CorruptionChallenge5].[dbo].[Orders]
WHERE id > -2147471026
-- 181 Rows

Now check to be sure we got it all.

-- Step 11
--Check no missing IDs in original data (using
Select id
FROM [CorruptionChallenge5_Original].[dbo].[Orders] -- -2147471026
EXCEPT
Select id
FROM [CorruptionChallenge5].[dbo].[Orders]
WHERE id <= -2147471026

Find out what is missing.

-- Step 12 Check Customers
Select * from CorruptionChallenge5_Original.dbo.Customers
EXCEPT
Select * from CorruptionChallenge5.dbo.Customers

Put the 181 missing rows back in.

-- Step 13 Insert missing 181 rows
Use CorruptionChallenge5_Original
GO

SET IDENTITY_INSERT Orders ON;

INSERT INTO dbo.Orders(id, orderDate, customerId, shippingType, orderDetails)
SELECT id, orderDate, customerId, shippingType, orderDetails
FROM [CorruptionChallenge5].[dbo].[Orders]
WHERE id > -2147471026

SET IDENTITY_INSERT Orders OFF;

We have the data back, so lets check the table.

/*
Customers table = 400 Records
Orders Table = 12803 Records
*/
-- Ensure no corruption
DBCC CHECKDB

-- FinalSteps drop the Corrupt version and rename fixed version

And that is how Patrick solved it this week.  Nice job Patrick.  I will post my solution which was different from this one soon.

Related Links

• Current Scores
• Newsletter Sign up
• Database Corruption Challenge T-Shirt
• Week 1, Week 2, Week 3, Week 4, Week 5
• Database Corruption Worksheet

Database Corruption Challenge Sponsored by

FitBit Stats: I walked 7820 steps (4.58 miles) on my treadmill at my walking desk while writing this post.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!