Week 7 Challenge Details

Challenge 7 Details

On to the Week 7 Corruption Challenge. For this challenge, any version of SQL Server from 2005 or newer will work, however I don’t know on Azure, and I haven’t tried it on SQL Server 2016 CTP2 yet. I did test it on SQL Server 2005 and on SQL Server 2014.

Not all missing data is caused by hardware failures or issues, sometimes it is caused by people. – Unknown DBA

Here are the challenge details for this week.

During the following steps work was being performed on the database.

5:50PM a full backup was performed.
5:51PM transaction log backup.
5:52PM transaction log backup.
5:53PM transaction log backup.
5:54PM transaction log backup.
5:55PM transaction log backup.
5:56PM transaction log backup.
Around 5:57PM to 5:58pm data from the [OPEN_NFIRS].[Record1000] users reported the table was no longer able to be queried, red error messages when selecting from [OPEN_NFIRS].[Record1000]. Soon thereafter that the data in the table was missing. Someone noticed that the table was empty, then turned off transaction log backups, and put the database in single user mode to prevent any more changes.
Around 5:59PM the database was detached, and the MDF, LDF, and NDF files were copied off.

Your goal if you choose to accept it is to get restore all the data in the [OPEN_NFIRS].[Record1000] table prior to its disappearance. Note: there may have been multiple DBA’s working on this system in several different time zones across the world.

Submitting Your Solution

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 or to restore the missing data, and that you didn’t lose any data.

SELECT COUNT([Record1000Id]) as check1
      ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2
      ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3
      ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4
      ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5
      ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6
      ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7
      ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8
      ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9
FROM [OPEN_NFIRS].[Record1000];

Mail your results to before Saturday June 6th, 2015 at 11:59pm pacific time. Results will be posted the next morning.

Competition Dates

The competition begins at 6:00am (Pacific time) on Tuesday June 2^nd. The competition ends on Saturday June 6^th at 11:59pm (Pacific time).

Additional Clues

An additional clue was added at 6:50pm on 6/4/2015 if you need it. This was 61 hours after the challenge started.

Updated Scoring

After Week 6, I have decided to modify the scoring going forward, additional details added in bold.

Each week you can earn the following points:
- 2 points for being the first to submit a correct solution, as long as you mention the contest on twitter, with a link.
- 1 point for submitting a correct solution before the deadline.
- Bonus: 1 extra point to everyone who completes the challenge before any extra clues are provided. I will wait at least 24 hours before posting any extra clues.
- There may be additional points described an a challenge description.
Once in the competition you can earn points for the following:
- 1 point for creating a blog post or LinkedIn post that talks about the challenge, and links to http://SteveStedman.com/corruption (if you do this you should email a link to the post). For the LinkedIn Post, status updates don’t count, you need to click the “Publish a Post” link.

The LinkedIn option was added when some people commented on the fact that they don’t have a blog, and the “Publish a Post” feature on LinkedIn is similar to a blog post.

The view the current score leader board, visit the Current Scores page.

Bonus Point

This week there is a bonus point that can be earned at any point before the competition ends. If once you have the correct solution, you can determine what happened to all the data in the [OPEN_NFIRS].[Record1000] table, and who did it, you will then earn one extra point for the week.

File Download

The zip file contains a single full backup, several transaction log files, MDF, LDF, and NDF files.

CorruptionChallenge7.zip

And Finally… What about the t-shirts

A while back I announced that there would be T-Shirts awarded as a prize, then number of shirts would depend on the number of participants. As we get closer I am finalizing the number of shirts that will be awarded. Right now I am thinking that I will award shirts to at least the top 10 scoring participants. I will hand these out to the winners at PASS Summit in October. For those who won’t be attending PASS Summit, I can ship the shirt to you, however there may be additional shipping charges depending on where you live.

One more note…

I am incredibly busy this week. I will get to all your emails regarding the challenge, but I won’t be able to respond much during normal working hours, I am onsite with working with a client most of this week. Please be patient with me, all results will be scored in the order that they were received.

Winners:

This week the first winning solution that included requirements above was from Raul Gonzalez. Great work Raul !!! The solution was submitted just 58 minutes after the challenge began.

Winner #1 Raul Gonzalez