You can download a zip file with a single full backup file created SQL Server 2005 that can be restored and used on SQL 2008, SQL Server 2008R2, SQL Server 2012 or SQL Server 2014. I have confirmed it on SQL Server 2005, and SQL Server 2014.
The story, corruption was encountered running DBCC CheckDB that looked like this:
Msg 8938, Level 16, State 2, Line 1Table error: Page (1:1849), Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Unexpected page type 1.Msg 8976, Level 16, State 1, Line 1Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Page (1:1849) was not seen in the scan although its parent (1:1832) and previous (1:1848) refer to it. Check any previous errors.Msg 8978, Level 16, State 1, Line 1Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Page (1:1850) is missing a reference from previous page (1:1849). Possible chain linkage problem.CHECKDB found 0 allocation errors and 3 consistency errors in table 'Customers' (object ID 2073058421).CHECKDB found 0 allocation errors and 3 consistency errors in database 'CorruptionChallenge6'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionChallenge6).
Someone had disabled the SQL Server Agent, so there were no recent automated backups run on this database. After the corruption was encountered, a backup was performed in order to distribute the corrupt database for the competition. There were no transaction log backups, only the single full database backup that contains the corruption.
You may be tempted to just drop and recreate an index and call it done. Do you really think I would make it that easy, this is the Database Corruption Challenge.
How to know if you succeed
When you think you have success, run the following query and send me the results, along with 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.
SELECT COUNT(1) AS check1, COUNT(DISTINCT FirstName) AS check2, COUNT(DISTINCT MiddleName) AS check3, COUNT(DISTINCT LastName) AS check4, CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5, CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6, CHECKSUM_AGG(CHECKSUM(LastName)) AS check7 FROM [Customers];
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.
There is a bonus point available this week for adding a comment to the DBCC CheckDB speed blog post. All who answer the question of what you do when CheckDB is slow and post it as a response to the blog, will earn a bonus point.
To Submit Your Results.
If you are having difficult and need an extra clue, there is an extra clue available. Don’t view the extra clue unless you really need it. The extra clue was added on May 25th at 1:30pm (pacific time).
The zip file contains a single full backup. See the challenge details for a description.
- Current Scores
- Newsletter Sign up
- Database Corruption Challenge T-Shirt
- Week 1, Week 2, Week 3, Week 4, Week 5
- Slow DBCC CheckDB
- Database Corruption Worksheet