Week 2 Challenge Details

Week 2 Challenge:

You can download a zip file with multiple backup files created on SQL Server 2008 that can be restored and used on SQL 2008, SQL Server 2008R2, SQL Server 2012 or SQL Server 2014.

The story… A backup was run yesterday, after that backup several changes were made to the database, inserts deletes, updates, and more. Sometime today corruption was encountered when someone attempted to run the following query:

SELECT * FROM Revenue;

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: invalid protection option. It occurred during a read of page (1:244) in database ID 21 at offset 0x000000001e8000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CorruptionChallenge2.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.

You know its never good when its in red.

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 two full backups.

 

Testing your solution.

New in week 2:  I have added a stored procedure to test the database to let you know if you have removed the corruption without losing anything.

USE CorruptionChallenge2;
GO
EXEC checkCorruptionChallenge2Result;

This stored procedure will create various outputs depending on the condition of the database and corruption. If everything checks out fine there will be a message that states to send me an email that includes your solution. If the stored procedure doesn’t exist then you don’t have the right starting point.

 

The zip file contains 2 backups. See the challenge details for a description.

Note, even though it states that one is a two day ago backup, the time stamps on the file don’t exactly show that. It is just simulating an older backup.

Here is the zip file with the corrupt database: CorruptionChallenge2.zip

Good luck!

This challenge is closed.

Week 2 Solutions:

• The Winning Solution – Week 2
• Alternate Solution – Week 2

Related Links:

• Week 3 Corruption Challenge
• Week 2 Corruption Challenge
• Week 1 Corruption Challenge
• Database Corruption
• Database Corruption Worksheet
• Stedman Solutions Database Emergency Services

Getting Help from Steve and the Team

Contact us for your Free 30 minute consultation OR
schedule your appointment now.