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

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:

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:

 

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.

 

 

---

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!