Week 2 Database Corruption Challenge
Welcome to the second Database Corruption Challenge (DBCC), this is the second week in an about weekly competition. Here is how it works; I have created a corrupt database, hopefully more corrupt or more challenging than the previous week. I then solved the corruption myself, usually in 2 or 3 different ways in order to prove that it is possible to fix, without data loss.
I then post the challenge including the database download and announce it. Those who subscribe to my newsletter will get advance notice to know when it is going to be posted.
For those who competed last week, you may recognize the Revenue table, however there are a number of changes over the previous challenge. If you think you recognize the database, please look around and be sure you see the differences over last week.
The challenge will be to download the corrupt database and attempt to recover it. If you can recover it, please send me the steps you used to recover the database, 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.
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. Others who send me a correct solution by the deadline will also get their name posted. 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. Unless you specify otherwise, anything you send me may be used on the blog to tell the story of how to solve the corruption.
Week 2 Database Corruption Challenge details.
Winners:
#1 Rob Farley
- #2 – Nicolette Carpenter Boddie
- #3 – Parikshit Savjani
- #4 – Ivan Rodriguez Camejo
- Subhro Saha
- Neil Abrahams
- Rui Bastos
- Lucas Kartawidjaja
- Piche Sebastien
- Bogdan Sahlean
- Raul Gonzalez
- Pasquale Ceglie
- David Patterson
- Patrick Flynn
- Arthur Baan
- André Kamman
- Prageet Surheley
I would like to point out that Bogdan Sahlean was the first to solve using DBCC Page to find the exact corrupt bytes, and DBCC WritePage to fix those corrupt bytes.
There is still time to learn. Give the Database Corruption Challenge a try.
Related Links
- Current Scores
- Newsletter Sign up
- Database Corruption Challenge T-Shirt
- Week 1, Week 2, Week 3, Week 4, Week 5, Week 6, Week 7, Week 8, Week 9, Week 10
- Slow DBCC CheckDB
- Database Corruption Worksheet
Database Corruption Challenge Sponsored by
Getting Help from Steve and the Team
Contact us for your Free 30 minute consultation ORschedule your appointment now.
Hi Steve, you should consider the time zone of the various countries when you post the challenge :-)
Yes. Good point. It turns out that there is no time that is good for everyone in every timezone. Each week I pick a different time than the previous week so that nobody in a single time zone will have an advantage.
The first week the Corruption Challenge launched on Saturday at about 9:00am (pacific time).
The second week the Challenge launched on Friday at 5:00pm (pacific time).
Tomorrow I will be announcing the time that this weeks Corruption Challenge will be launched, but only those who subscribe to the newsletter will get advance notice.
Find out about the newsletter
Love your challenge and the amount of effort and coordination you have put into it. Amazing Job Steve. The fact you even thought about time zones for a challenge like this blows me away. All I can say is Bravo!
Thanks for the feedback Raymond. I will keep the challenges coming.
With the solution provided how you are sure that the data didn’t get updated between the time of the last backup and when the corruption occurred. because the solution insert the messing row from the old backup
Mo – great question.
In a real world scenario, you might have more details to show what was done recently, but for challenge #2, I provided a stored procedure, to determine if everything had been restored correctly.
EXEC dbo.checkCorruptionChallenge2Result;
The stored procedure was simulating a real world check to see if the latest orders or latest updates were included in the database.
I hope this helps answer your question.
-Steve Stedman