Corruption Challenge 1 – An alternative solution

After posting the winning solution for Corruption Challenge 1 from Brent Ozar, I realized that he and I both solved the corruption by using the REPAIR_ALLOW_DATA_LOSS option on CheckDb. A very nasty move, however it did repair the corruption.

DBCC CHECKDB ('',REPAIR_ALLOW_DATA_LOSS);

After reading some feedback, one of the winners stated:

As soon as he ran REPAIR_ALLOW_DATA_LOSS, I knew we weren’t on the same page. I just never do that unless I’ve exhausted all the other options.

Which is a good point, in this solution I was fairly certain as to what REPAIR_ALLOW_DATA_LOSS was going to do, however in a real world scenario, who knows what might be effected beyond the initial table that we know about.

There are several other options to clean up the corrupt table besides the REPAIR_ALLOW_DATA_LOSS option. These options still involve copying the data off to another table and finding the missing data from row 31, however how the corruption gets cleaned up varies widely with the following options:

• Drop the table and indexes, rename the table that is holding the temporary data to be called Revenue, then recreate the indexes.
• Drop the clustered index, and put it back, then fix up row 31 with the missing data.
• Truncate the able, then insert all of the rows from the temporary location into the original table.
• Restore a second copy of the corrupt database, rebuild the original table, then just copy the data in from the other database.

Let’s take a look at a solution provided by Andre Kamman.

-- Database Corruption Challenge - Week 1 - Script by Andre Kamman
USE [CorruptionChallenge1]
GO

-- Create table to store recovered data in
CREATE TABLE [dbo].[Revenue_Recovered](
[id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentID] [int] NULL,
[Revenue] [int] NULL,
[Year] [int] NULL,
[Notes] [varchar](300) NULL
) ON [PRIMARY]

GO

-- It has an Identity Columns, so we need to enable identity_insert to be able to insert our own values
SET IDENTITY_INSERT dbo.Revenue_Recovered ON;
go
-- Recreate the corrupt table from the other 2 indexes who together happen to have all the columns.
-- We can join on the Id, there are no unique indexes on this table but I noticed the identity column and I checked for uniqueness before constructing this query
-- The hints ensure that the data comes from the index we select. SQL Server will not use the corrupt index to fetch data from in this case
INSERT INTO revenue_recovered(id, DepartmentId, Revenue, Year, Notes)
SELECT
ix2.id
,ix2.DepartmentID
,ix2.Revenue
,ix3.Year
,ix3.Notes
FROM dbo.Revenue ix2 WITH(INDEX(2))
JOIN dbo.Revenue ix3 WITH(INDEX(3)) ON ix3.id = ix2.id
ORDER BY ix2.id

go

-- Done, switch the identity insert off again
SET IDENTITY_INSERT dbo.Revenue_Recovered OFF;
GO

At this point he used a very similar solution to saving off the good data from the nonclustered indexes.

-- Drop the corrupt table and it's indexes (in a real life situation this could be more complex, think of having to recreate triggers, foreign key's etc.)
DROP TABLE dbo.Revenue
go

-- Rename the table that holds the recovered data to the original name
EXEC sp_rename 'Revenue_Recovered', 'Revenue'
go

-- And finally we can recreate the 3 original indexes and we're done...
CREATE CLUSTERED INDEX [clustId] ON [dbo].[Revenue]
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [ncDeptIdYear] ON [dbo].[Revenue]
(
[DepartmentID] ASC,
[Revenue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [ncBadNameForAnIndex] ON [dbo].[Revenue]
(
[Year] ASC
)
INCLUDE ([Notes]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

At this point the corruption has been cleaned up, and all of the data is there, no data loss at all. Another winning solution, perhaps something different to try depending on your corruption issues.  (I mean your database corruption issues.)

If you want to find out about the next corruption challenge, stick around, or register for my newsletter to be informed of when the challenge will begin.

Related Links:

• Newsletter sign up
• Database Corruption Challenge 1
• Corruption Challenge 1: How I corrupted the database
• Corruption Challenge 1: The winning solution

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!