Skip to content

Corruption Challenge Week 4 – The Winning Solution

Congratulations to Randolph West who won the corruption challenge this week with the following solution which restored all of the data.

First he restored the database to get started. Note some of his code and comments have been reformatted to better fit in the blog format.

Restore database. I use KEEP_CDC and KEEP_REPLICATION because of the hints you dropped in your blog.
The hint is that there is 100% chance of data recovery, so I will look for Change Data Capture tables.

USE master;
GO
--DROP DATABASE [CorruptionChallenge4] ;
RESTORE DATABASE [CorruptionChallenge4]
   FROM DISK = N'C:\DBBackups\CorruptionChallenge4_Corrupt.bak'
   WITH FILE = 1,
        MOVE N'CorruptionChallenge4'
		  TO N'C:\SQL_DATA\CorruptionChallenge4.mdf',
        MOVE N'UserObjects'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf',
        MOVE N'CorruptionChallenge4_log'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
        NOUNLOAD,
    REPLACE,
    STATS = 5,
    KEEP_CDC,
    KEEP_REPLICATION;

Next he ran CheckDB to see what is wrong with this database.

Read More »Corruption Challenge Week 4 – The Winning Solution

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

Week 4 – Building the Corrupt Database

The following are the steps that I went through to create a more difficult Database Corruption Challenge for Week 4.
Here is how I built the database, nothing fancy, however I did add an extra FILEGROUP called [UserObjects].

CREATE DATABASE [CorruptionChallenge4]
 ON  PRIMARY
( NAME = N'CorruptionChallenge4',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4.mdf',
  SIZE = 4288KB ,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1024KB ),
FILEGROUP [UserObjects]  DEFAULT
( NAME = N'UserObjects',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf' ,
  SIZE = 4096KB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1024KB )
LOG ON
( NAME = N'CorruptionChallenge4_log',
  FILENAME = N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
  SIZE = 1088KB,
  MAXSIZE = 2048GB,
  FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CS_AS;
GO

Notice, that to make things slightly more challenging, the database is a Case Sensitive Accent Sensitive database. I hope this didn’t cause too much trouble.
Next I created a couple of tables.

Read More »Week 4 – Building the Corrupt Database

Database Corruption Challenge T-Shirt

The Database Corruption Challenge (DBCC 2015) t-shirt is now available at CafePress. It looks something like this.

CorruptionChallengeShirt

Check it out and order one to show your participation in the Database Corruption Challenge.

Last week, I mentioned that I would come up with prizes for the contest, and that the prizes would be based on the number of participants each week. So here is what I have come up with:

Read More »Database Corruption Challenge T-Shirt

Bellingham SQL Server Users Group – May 20th

The Bellingham SQL Server Users Group will be having its next meeting on May 20th at 6:00pm. This is free event for the local SQL Server community.

What is the Bellingham SQL Server Users Group?

PASS Chapters are local user groups that meet regularly in communities around the world to share their passion and knowledge about Microsoft SQL Server and Business Intelligence. Each Chapter has its own style, leadership, and schedule to meet members’ needs. PASS Chapters are a free and easily accessible resource for learning more about SQL Server and connecting with other SQL Server professionals in your area. They are also your local connection to the PASS Community.

Monthly Chapter Meeting

Read More »Bellingham SQL Server Users Group – May 20th