Skip to content

SQL server corruption

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