Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge

Download PDF

corruption

Welcome to the DataBase Corruption Challenge, this is an about weekly blog challenge where I will post a corrupt SQL Server database with some details on what happened to it.

If at this point you are already a bit irked by my use of capitalization in the DataBase Corruption Challenge, and the acronym of DBCC that I have used to describe it, then you are already ahead of many people reading about this challenge. Welcome to the challenge.
The challenge will be to download the corrupt database and attempt to recover it. If you can recover it, please send me the steps to recover it, along with some proof that the database has been recovered. The goal each week will be the following:

  • Recover the database to your best ability.
  • You need to be able to bring the database online and run queries against it.
  • For any corruption that causes data loss, you need to figure out how to restore as much of the missing data with what you have been given.
  • When you come up with your solution, send me your result by email. Tell me what you did to fix the corruption, what you did to restore data (if that as needed), and include proof. The proof could be a screen shot, showing the solution, it could be a TSQL Script with the solution, or something else. I will validate and confirm if your result is correct. Depending on the number of people who send me results, this may take a while.

Goals are subject to change over time depending on how things go.

The first person who sends me a correct result will get their name posted on my blog as the winner of this weeks DataBase Corruption Challenge. Some of the first, accurate and unique solutions will be posted on the resulting blog post. If you don’t wish to have your name associated with your results, please ask to remain anonymous, and I will not use your name.

In order to simulate a real world scenario, some corrupt databases will have no backups, others will have no recent backups. Some weeks there might a single corruption problem, in other weeks it might be more complex.

Also, please not that before each challenge was posted, I have created at least one solution. So it is possible to complete the challenge.

Let’s start with the challenge for week 1.

You are given a .bak file with a corrupt database. That is all you have access to, the original database was already destroyed by a network admin attempting to solve the problem. This backup file was run on SQL Server 2014.

CorruptionChallenge1.zip  (download the backup file)

For SQL Server 2008, 2008R2, or 2012 you can download a 2008 backup file to use.

Here is the script that I used to restore the database.

USE master;
GO
-- you may need to change the path to the actual download location.
RESTORE DATABASE CorruptionChallenge1
 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge1.bak'
 WITH RECOVERY;

Which looked something like this:

restore

To restore to another SQL Server with SQL installed on the D: drive, I used this command to move the database files to the new location:


USE [master];

RESTORE DATABASE [CorruptionChallenge1]
FROM DISK = N'D:\SQL2014\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge1.bak'
WITH FILE = 1,
MOVE N'CorruptionChallenge1' TO N'D:\SQL2014\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge1.mdf',
MOVE N'CorruptionChallenge1_log' TO N'D:\SQL2014\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge1_log.ldf',
RECOVERY;

 

Once the database was restored, I confirmed that it was indeed corrupt with the following check.


USE CorruptionChallenge1;
DBCC CheckDB() with no_infomsgs;

Which confirmed the following:

CorruptWeek1Confirmed

 

Msg 8944, Level 16, State 13, Line 2
Table error: Object ID 245575913, index ID 1, partition ID 72057594040614912, alloc unit ID 72057594045857792
(type In-row data), page (1:280), row 3. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 3139 and 288.
Msg 8944, Level 16, State 13, Line 2
Table error: Object ID 245575913, index ID 1, partition ID 72057594040614912, alloc unit ID 72057594045857792
(type In-row data), page (1:280), row 3. Test (ColumnOffsets <= (nextRec – pRec)) failed. Values are 3139 and 288.
Msg 8928, Level 16, State 1, Line 2
Object ID 245575913, index ID 1, partition ID 72057594040614912, alloc unit ID 72057594045857792 (type In-row
data): Page (1:280) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 245575913, index ID 1, partition ID 72057594040614912, alloc unit ID 72057594045857792
(type In-row data). Page (1:280) was not seen in the scan although its parent (1:281) and previous (1:246) refer
to it. Check any previous errors.
CHECKDB found 0 allocation errors and 4 consistency errors in table ‘Revenue’ (object ID 245575913).
CHECKDB found 0 allocation errors and 4 consistency errors in database ‘CorruptionChallenge1’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionChallenge1).

 

Where do you go from here? Give it your best shot.

Winners

The first correct answer was provided by Brent Ozar, he did submit a winning solution even though he only had 30 minutes to spare before dinner. Excellent work!

Here is the list of those who had correct answers with no data loss:

#1 – Brent Ozar

  • Patrick Keisler
  • Rob Farley
  • Parikshit Savjani
  • Sébastien Piché
  • Ivan Rodriguez Camejo
  • Dave Walden
  • John Morehouse
  • Subhro Saha
  • Robin Watkins
  • Jon Gurgul
  • André Kamman
  • Damian Widera
  • Rui Bastos
  • Neil Abrahams
  • Nicolette Carpenter Boddie
  • wBob
  • Walden H. Leverich
  • Michel Bruggeman
  • Shawn Johnson
  • John Sterrett
  • Bogdan Sahlean
  • Dan Brennan
  • Pedro Ferreira

Time has run out on the competition. Congratulations to everyone who participated. To see the winning solution visit this post.

Notes

There has been a request from one of the participants that Paul Randal be disqualified from competing. Given that we are all in this to learn more, I would actually encourage Paul to submit a solution to this weeks Database Corruption Challenge. I am sure we would all learn something from the experience.

 

 

If you have any questions please email me at the address shown above.

 

Posted in Corruption, SQL 2014 Tagged with: , , , , , , ,
13 comments on “Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge
  1. Brent Ozar says:

    Hahaha, awesome. Great idea! Looking forward to seeing what folks come up with.

  2. ashish malwal says:

    Hi Steve thanks for sharing this but the backup file that you have download with this blog is not valid i am unable to restore. could you please check and confirm if its valid or only for me its not working . i have check with verifyonly and headonly as well and both command showing its not valid backup file .

    *** INCOMPLETE ***

    Msg 3241, Level 16, State 13, Line 1
    The media family on device ‘E:\CorruptionChallenge1.bak’ is incorrectly formed. SQL Server cannot process this media family.
    Msg 3013, Level 16, State 1, Line 1
    VERIFY DATABASE is terminating abnormally.

    • SteveStedman SteveStedman says:

      What version of SQL Server are you attempting to restore to. The backup is fine, many have downloaded it and successfully restored it. This is a SQL 2014 backup. I am working on a SQL Server 2008 backup that will be available soon.

      • ashish malwal says:

        Ohhh that’s my bad i am restoring it on 2012 and thanks i will wait for 2008 backup thanks :)

        • ashish malwal says:

          Hi Steve thanks lot for 2008 .bak file and the fix done sent you an email please check and thanks lot for sharing this scenario :)

  3. Jon Gurgul says:

    haha can not resist these :-)

  4. Walden Leverich says:

    Love it!

    Solution sent. But please don’t post these on a weekend, only gives us a small window on Monday morning. Though it didn’t take long (assuming I got it right. :-)

  5. Dan says:

    Thanks for setting this up. It was a fun exercise.

  6. It’s a challenge?! I’m in! :)

  7. Ponraj Veluchamy says:

    By dropping cluster index and recreating index it got fixed.

  8. Purclot says:

    hi Steve,
    it’s a great idea with the challenges! I’ve started with the
    Day 1 and analyzed the Brent’s solution. What that means is: the data structure stayed intact, “only” the index structure was broken (the pointers to the missing records)?
    Andy

    • SteveStedman SteveStedman says:

      Since the index is a copy of the data, comparing the index to the table showed the problem. My the structure being “intact”, that was referring to the fact that you could still query the table without getting any errors. Does that help?

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.