SteveStedman

Welcome to the seventh 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.

New Participants

For any first time participants in Week 7, you can be part of a special prize group. I realize for those who are joining the competition now it isn’t possible to score enough points to compete with the current leaders. I will post a separate 3 week leader board for the last 3 weeks of the competition. To be considered in this section, you need to meet the following criteria:

• You have not competed in the Database Corruption Challenge in past week
• You must be subscribed to my newsletter so that you can get updated on the upcoming challenges.
• You must participate in the remaining challenges (7, 8, 9 and 10). You don’t have to succeed in all of the remaining weeks, but you do need to try.

The Challenge

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 »Week 7 Database Corruption Challenge

If you are looking for more info on the corrupt database with Week 6 of the Database Corruption Challenge, you can take a look at the original post.

Week 6 was won by Raul Gonzalez who submitted his winning solution just 45 minutes after the challenge began. Raul has solved 4 of the 6 challenges so far, and has scored 2 extra points for linking to the challenge, and commenting on the CheckDB post.

The solution this week was in the non-clustered index, and there was some corruption in the clustered index. If you were to just drop and recreate the non-clustered index, the corruption in the clustered index was not able to be found. Comparing the values in the non-clustered index to the clustered index show where the corruption exists.

Lets take a look at his solution. The only thing I changes was the directory paths on the restore to match my configuration.

USE master
GO
IF DB_ID('CorruptionChallenge6') IS NOT NULL BEGIN
	ALTER DATABASE CorruptionChallenge6 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE CorruptionChallenge6
END
GO

IF OBJECT_ID('tempdb..#fromIndex') IS NOT NULL	DROP TABLE #fromIndex
IF OBJECT_ID('tempdb..#fromTable') IS NOT NULL	DROP TABLE #fromTable
IF OBJECT_ID('tempdb..#goodData') IS NOT NULL	DROP TABLE #goodData

RESTORE FILELISTONLY
FROM DISK = 'C:\DBBackups\CorruptionChallenge6.bak'
GO

RESTORE DATABASE CorruptionChallenge6
FROM DISK = 'C:\DBBackups\CorruptionChallenge6.bak'
WITH NORECOVERY, REPLACE
	, MOVE 'CorruptionChallenge6' TO 'C:\SQL_DATA\CorruptionChallenge6.mdf'
	, MOVE 'CorruptionChallenge6_log' TO 'C:\SQL_DATA\CorruptionChallenge6_log.ldf'
GO

RESTORE DATABASE CorruptionChallenge6 WITH RECOVERY
GO

Read More »Week 6 – The Winning Solution – Database Corruption Challenge

There are many times that CheckDB ends up being extremely slow, sometimes taking more than a day to run. This can make life difficult if you are trying to find out what is corrupt.

DBCC CheckDB(MyBigDatabase) WITH NO_INFOMSGS;

There are several of the tricks that I use to speed up DBCC CheckDB, depending on the specific environment. What I am looking for is what others do when they need to run DBCC CheckDB on a big database that appears to take forever to complete?

Read More »Slow CheckDB – What do you do?