Skip to content

Corruption Challenge

Corruption Challenge #8

Welcome to the eighth week of 10 in the Database Corruption Challenge (DBCC), this is an about weekly competition. Here is how it works; I have created a corrupt database, hopefully more corrupt or more interesting than the previous week. I then solved the corruption myself in order to prove that it is possible to fix, without data loss. There is a total of 4 points available to be earned this week.

  • 1 point extra for being the first correct answer. Already taken by Rob Farley.
  • 1 point to all who get the correct answer.
  • 1 point extra if you get the correct answer before any hints are given.
  • 1 point extra if your solution is correct the first time you submit it to me.  (check your work)

Week8

Corruption Challenge General Info

The challenge will be to download the corrupt or somehow damaged database and attempt to recover it. If you can recover it, please send me the steps you used to recover the database, along with some proof that the database has been recovered. The goal each week will be the following:

Read More »Corruption Challenge #8

Corruption Challenge 4 – Second Alternate Solution.

The following is a solution to Corruption Challenge #4 that uses DBCC WritePage to fix the corruption.  Before proceeding, be sure to read this warning.

WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.

DBCC WritePage is a cool, but extremely risky way to solve this weeks Database Corruption Challenge, however, do NOT EVER RUN THIS ON A PRODUCTION SYSTEM.

Read More »Corruption Challenge 4 – Second Alternate Solution.

Corruption Challenge 4 – Alternate Solution

Here is the solution provided by Patrick Flynn. Congratulations Patrick for being one of this weeks winners.

I really liked this solution for a number  of reasons, first it showed how to pull data from DBCC Page and actually convert it into rows and columns that could then be inserted back into a table. Secondly due to its use of Common Table Expressions.

 

This was done using SQL Server 2014.

Approach used was:

(a) Restore and check database – Corruption is related to Allocation Meta Data for dbo.Customers

(b) Use the non-clustered indexes on FirstName and LastName to recover data apart from Middle name – Total of 511740 rows in Table
Steps are in CorruptionChallange Setup.sql (requires disabling Database triggers

(c) Cannot directly select data from pages due to allocation issues but can select raw data vis DBCC Page (and DBCC IND)
Requires using DBCC Ind to get list of Data pages and DBCC Page to extract data
Steps to extract and Parse data are in DBCCPage_Extract

Read More »Corruption Challenge 4 – Alternate Solution