Week 5 – Winning Solution – Database Corruption Challenge

Download PDF

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

Database Corruption Challenge Sponsored by
StedmanSolutionsRemoteSkilledDBA


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

 

More from Stedman Solutions:

SteveStedman5
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!

4 Comments on “Week 5 – Winning Solution – Database Corruption Challenge

  1. I tried to attach with replace when looking at this challenge, but had to hex edit page 9 to get a solution.

    I always got:

    “Msg 5173, Level 16, State 1, Line 66
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.”

    Even using solution posted here and it does not work for me.

    Microsoft SQL Server 2014 – 12.0.2456.0 (X64)
    Dec 11 2014 17:32:03
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

  2. Hi steve,
    this solutions is not working for me either. i had the same issue from the day1.
    i tried on sql 2008R2, 2012,2014 all on Enterprise edition.
    i replaced the .ndf & .ldf as mentioned above.
    then i am getting errors and database is still in recovery pending below is the error message:
    — Step 5 Bring Online
    ALTER Database CorruptionChallenge5 SET ONLINE
    — Comes up as suspect
    ***********************************************************************************
    Msg 5173, Level 16, State 1, Line 1
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
    Msg 5173, Level 16, State 1, Line 1
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
    Log file ‘G:\SQLULogs\CorruptionChallenge5_log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
    Msg 5181, Level 16, State 5, Line 1
    Could not restart database “CorruptionChallenge5”. Reverting to the previous status.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.
    *****************************************************************************
    then i tried to set emergencu mode: it gave error but still set teh database to emergency when i refresh the databases.
    *************************************************************
    ALTER Database CorruptionChallenge5 SET EMERGENCY

    Msg 5173, Level 16, State 1, Line 1
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
    *************************************************************************************************
    this is the tough bit never able to run the checkdb: below is the error message
    DBCC CHECKDB(‘CorruptionChallenge5’) WITH NO_INFOMSGS, ALL_ERRORMSGS
    *********************************************************************************
    Msg 945, Level 14, State 2, Line 1
    Database ‘CorruptionChallenge5’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    ****************************************************************************

    winning solution was performed on developer edition.
    Jon Gurgul: tried on Enterprise edition.
    Does any one solved this using enterprise edition.
    if so steve can you figure out what is wrong here.

    thanks
    kumar

  3. I got the same as Jon and kumar above. I was using SQL 2014 Express Edition.

    Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

    After Step 5 above, I get:

    Msg 5173, Level 16, State 1, Line 23
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
    Msg 5173, Level 16, State 1, Line 23
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
    Log file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSANDBOX\MSSQL\Log\CorruptionChallenge5_log.ldf’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
    Msg 5181, Level 16, State 5, Line 23
    Could not restart database “CorruptionChallenge5”. Reverting to the previous status.
    Msg 5069, Level 16, State 1, Line 23
    ALTER DATABASE statement failed.

    Try to run step 6 results in:
    Msg 5173, Level 16, State 1, Line 23
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    Try to run step 7 results in:
    Msg 945, Level 14, State 2, Line 25
    Database ‘CorruptionChallenge5’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

    I’d love to know if there was something I was doing wrong.

    Thank you
    Dave

  4. There were many issues with getting the configuration exactly right for this weeks solution. Based on the amount of time it took me to set it up and test on the various version, I should have known there was a problem with it just being a nightmare configuration.

    I don’t have a good answer for why this didn’t work for some, but it did work for others.

    Based on Week 5 being difficult due to getting the configuration right, I have added another way to score an extra point in challenge #6.
    http://stevestedman.com/S4r0J

    Sorry that challenge #5 was so ugly. Let’s move on to #6.

Leave a Reply

Your email address will not be published. Required fields are marked *

*