Week 3 Challenge Details

The database name is [CorruptionChallenge3], it was created and backed up on SQL Server 2005. I used SQL 2005 to accommodate those challenge participants who don’t have the latest version of SQL Server.

Here is the timeline as things occurred

At 2:53pm a full backup was performed. After that full backup, users continued to do work and the database continued to grow.

At 2:54pm a transaction log backup was performed. Followed by more changes to the database.

At 3:01pm another transaction log backup was performed. Followed by more changes to the database.

At 3:12 another transaction log backup was performed. Followed by more changes to the database.

At 3:19 a catastrophic failure occurred causing the operating system running SQL Server to reboot. Let’s blame it on a power surge, or maybe it was just a DBA who accidentally bumped the power cord. I like the power surge idea, so that I don’t get blamed for kicking the power cord.

After the server rebooted, several other databases on this SQL Server recovered just fine, however the CorruptionChallenge3 database would not come on line. After examining the SQL Server data directory, it was discovered that the single .mdf file for this database was gone, missing, just not there. The only thing that remained on this SQL Server was the .ldf file for this database.

The server was left in a Recovery Pending state when the SQL Server restarted. Assume that the DBA at this point simply detached the database, and all that was left was the files included in the downloadable zip file.

Is it possible?

I would like to state at this point that it is entirely possible to recover 100% of the data. Also 100% of the data can be recovered without the dirty tricks of DBCC WritePage (that’s a clue, you don’t need it). When I tested the recovery I was able to do it on SQL Server 2012, and SQL Server 2014 to confirm that it works, it should work on any SQL Server 2005 or newer, however I didn’t test it on Azure.

What you are given:

The full backup file, 3 log files, and the .ldf file that was left in the data directory. All of these files were created using SQL Server 2005, and they should work on SQL Server 2008, 2008R2, 2012 and 2014.

Download the zip containing these 5 files.

Download the corrupt database files here: CorruptionChallenge3.zip

How to know if you succeed

Last week the competition may have been made easier because there was a stored procedure to judge success. This week there will not be any script to check it. All the information you need is contained in the 5 files you have been given.

All 5 files will be needed to recover all the data. If you haven’t pulled data from every one of these 5 files, then you are probably missing something.

Submitting your solution

Send me your solution, the TSQL or other steps you took to restore the data, and a screenshot, or proof showing the number of rows in the [History] table, with details on what the last several rows in that table look like.

Mail your results to before Monday April 27^th, 2015 at 10:00am pacific time.

I am going to be at a conference on Saturday, all submitted solutions will be ordered by when they arrive in my inbox with a correct solution. It may take a while to get back to you during the day on Saturday, due to the conference. I will attempt to check all the submitted solutions before leaving for the conference on Saturday morning.

Good luck!

Week 3 Challenge Details

Getting Help from Steve and the Team

Leave a Reply Cancel reply