Week 5 Challenge Details

The database name is [CorruptionChallenge5], it was created and backed up on one of the specific versions of SQL Server listed here. Be sure that you get a version to match your SQL Server. Sorry I don’t have any 2005 or 2008 support this time, and there isn’t support for every minor version.

Microsoft SQL Server 2014 – 12.0.2000.8
Microsoft SQL Server 2012 (SP1) – 11.0.3128.0
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1

Here is what we know. After the SQL Server stopped responding (unknown reason), someone decided to just restart the SQL Server Service. After the restart the [CorruptionChallenge5] database did not start and it was left in the Recovery Pending state. You have been called to fix it.

You might also see an error message that looks something like this:

Msg 5181, Level 16, State 5, Line 4
Could not restart database "CorruptionChallenge5". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.
Msg 824, Level 24, State 2, Line 4
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 7 at offset 0x00000000012000 in file 'C:\SQL_DATA\CorruptionChallenge5.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

As usual, you aren’t given much info, if you restore the .bak file you will get a working database with no corruption, however it is a bit out dated, there have been changes that need to be recovered since the last backup was taken.

Your job should you accept this challenge is to get the database back on line, and eliminate any corruption in the database without losing any data.

How will you know when you are successful? That’s part of the challenge, figuring out how to know if you succeeded.

Is it Possible?

I would like to state at this point that it is entirely possible to recover 100% of the data. Also 100% of the data can be recovered without the dirty tricks of DBCC WritePage (that’s a clue, you don’t need it). When I tested the recovery I was able to do it on SQL Server 2008R2, SQL Server 2012 and SQL Server 2014 to confirm that it works. Be sure to download the exact version to match your SQL Server.

What You Are Given

This week you are given a zip file with 5 files in it. A Full Backup of the database before the corruption occurred, a text file describing the exact version this was tested on. The mdf, ldf, and ndf files for the database that were copied after the “Recovery Pending” message was discovered.

Download the corrupt database files here:

I recommend the SQL Server 2014 12.0.2000.8 version since this is the SQL Server 14 RTM (Release to Manufacturing) version.

Microsoft SQL Server 2014 – 12.0.2000.8 Corruption Challenge #5

Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 Corruption Challenge #5

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 Corruption Challenge #5

Requirements:

You need the right matching SQL Server version.
Most any edition will work, there are not Enterprise features used in this challenge.

If you don’t have one of these versions, you can download the evaluation version of SQL Server 2014 from Microsoft.

Extra Clue…

24 hours after the challenge started, there were several requests for an extra clue. That extra clue has been provided, but please don’t visit the link for the extra clue unless you have already tried the challenge. At the time of the extra clue, 4 people have solved it and 4 have asked for extra clues.

How to know if you succeed

When you think you have success, there are four additional items that I need beyond the typical steps, and proof. These are:

What version of SQL Server did you use to fix the problem? SELECT @@VERSION;
How you removed the corruption?
What row or rows were impacted by the corruption?
How many rows do you have in the Orders table?

In order to win you must also provide the usual:

Detailed steps with what you did to complete the challenge.
Proof that you removed the corruption, and that you didn’t lose any data.
Provide a link to the corruption challenge url on Twitter, Facebook or Linked in (http://SteveStedman.com/corruption)

The first person to submit a winning solution with all this info will receive 2 points in the challenge; all those who submit correct answers who are not first will receive 1 point.

Mail your results to before Saturday at 8:00pm pacific time. Results will be posted soon after that. I will be at SQL Saturday in Redmond WA on Saturday, so don’t expect the final results and final judging to be completed until sometime on Sunday.

Getting Help from Steve and the Team

1 thought on “Week 5 Challenge Details”

Jon Gurgul May 18, 2015 at 03:07

Reply

“I would like to state at this point that it is entirely possible to recover 100% of the data. Also 100% of the data can be recovered without the dirty tricks of DBCC WritePage (that’s a clue, you don’t need it).”

http://stevestedman.com/server-health/database-corruption-challenge/week-5-database-corruption-challenge/week-5-challenge-details/

I think you would have thrown a few people off this week, by saying that you do not need to use dbcc writepage and thus rewrite that hex of the page. Then put forward the initial part of solution to use an editor to hex edit the page.

I personally discarded this solution based on your comment. Plus I have been unable to get the winning solution to work, without hitting an error trying to bring the database online. See my other comment.

Just like to add that I am really enjoying this series of blog post, and thank you for your hard work putting them together. If I could ask that you tag your blog posts with #CorruptionChallenge, #CorruptionChallengeSolution,#WeekX ,WeekXSolution etc as I struggle to find post sometimes.

Again thanks for the fun challenges, and looking forward to the next 5.