CDC

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