Skip to content

Database Corruption Challenge

Week 5 – Alternate Solution

Here is how I solved Week 5 of the Database Corruption Challenge. The following steps were tested and confirmed working on SQL Server 2008R2, SQL Server 2012, and SQL Server 2014.

 

To oversimplify, here are the steps:

  • Restore the last known good database.
  • Shut down the database, and copy off the last good database files.
  • Replace some files and restart it. (Hack Attach)
  • Next realizing that the boot page was corrupt page 1:9.
  • Shut down the database.
  • Copy the boot page from the last good database files and place it in the corrupt file.
  • Restart the database.
  • Realize there is other corruption.
  • Fix the other corruption.

Read More »Week 5 – Alternate Solution

Week 5 – Winning Solution – Database Corruption Challenge

Find out more about the Week 5 Challenge on the overview blog post.

The winning solution, was submitted by Patrick Flynn, just 3 hours and 18 minutes after the challenge was posted. Patrick provided me with the following steps, and TSQL code to back up the steps:

The process was

(a)    Restore two copies from backup (Old backup has 400 Customers and 1622 Orders)

(b)    Use the supplied MDF, LDF and NDF to hack attach to instance

(c)     Try using tail-log backup but log chain broken also as errors in Boot page (1:9) unable to use all files

(d)    Instead using original backup replace only log file and ndf file (containing user data)

(e)    After switching to emergency mode found errors in Orders table (1:175) but this does not contain new rows

(f)     Able to select the newest 181 rows and insert into original orders table

Final Status was

Orders = 12803 rows

Customers = 400 rows

No corruption remaining

Here is the exact TSQL code that supported that solution.

Read More »Week 5 – Winning Solution – Database Corruption Challenge

Database Health Monitor -Beta Version 10.1 Released Today

Today is the release of Database Health Monitor Beta 10.1. If you haven’t see the Database Health Monitor before, its a free application that I have been building over last 4 years.

It has been a while since the last release, so I thought I would just walk through and show a few of the features.

One thing that changed right off was in the previous version, these was a advertisement for my IndieGoGo campaign where I attempted to crowd fund the next phase of development on Database Health Reports. The IndieGoGo campaign has been removed.

Real Time Reports

The real time reports are a category of reports that query the database directly to find out something that is currently happening right now. See the Historic Reports below for the ability to find out what was happening at some point in the past.

One of the new reports added is the Suspect Paged report. If you have been participating the Database Corruption Challenge you will certainly understand the value of this report.

SuspectPages

Read More »Database Health Monitor -Beta Version 10.1 Released Today

Database Corruption Challenge Week 4 Results

It is my pleasure to announce the winners in the Week 4 Database Corruption Challenge. But first how about a rundown of challenge.

In order to make up for the difficult time that Challenge #3 was issued, Challenge #4 was issued at 2:00pm pacific time on a Friday afternoon. Those who had signed up for my Newsletter received early notification of when the corruption challenge would occur, but not details on what the challenge would be. When I launched it, I ran into a couple technical details, it appears that my WordPress site that hosts this blog was having difficulty with attachments larger that 8mb. So I ended up having to upload the file to another site where Database Health Monitor is hosted where the larger file was accepted. This delayed the start of the challenge about 20 minutes.

After the challenge released, it was quiet, almost to quiet. Then about 40 minutes into it, Randolph West messaged me on twitter to say that he was on it, and that he had discovered the CDC (Change Data Capture) tables. He also mentioned that he had dinner plans. He kept working on it, and at 4:55pm (pacific time) he provided me with proof that he had completed the challenge, however it wasn’t until 5:06pm (pacific time), that he provided me the full script showing his solution. At that point Randolph was the winner of the Week 4 Database Corruption Challenge. Two hours and 46 minutes after the challenge was able to be downloaded, Randolph had won the competition.

Read More »Database Corruption Challenge Week 4 Results