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.
USE [CorruptionChallenge4]; -- Run DBCC CHECKDB to see what's wrong DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY
SELECT * FROM sys.objects WHERE [object_id] = 2105058535
dbo.Customers seems to be the problem. Let’s cheat a little and look for a CDC table using this name
SELECT * FROM sys.tables
dbo_Customers_CT … 373576369 … 2015-05-01 08:19:32.570 … 2015-05-01 15:24:30.177
This looks promising. Let’s get the CDC output for net changes on this table, and dump them into a temp table.
We get the Start LSN from sys.fn_cdc_get_min_lsn(‘Customers’)
SELECT id, FirstName, MiddleName, LastName INTO tempdb.dbo.fff FROM [cdc].[fn_cdc_get_net_changes_dbo_Customers](0x0000009A000009700267, sys.fn_cdc_get_max_lsn(), 'all')
Which inserted 511740 rows into the tempdb.dbo.fff table.
Hmmm … how many records is that compared to the original corrupt table?
SELECT COUNT(*) FROM dbo.Customers SELECT COUNT(*) FROM tempdb.dbo.fff
SELECT * FROM dbo.Customers -- oops, page 3:22 is corrupt
SELECT * FROM dbo.Customers WHERE id IN (510900, 510901) -- oops, page 3:2150 is corrupt too
During my playing around earlier, I discovered that the two NC indexes were affected by corruption in page 3:88 as well.
SELECT * FROM tempdb.dbo.fff WHERE id IN (510900, 510901)
This is a mess – I guess we’ll have to turf the entire Customers table
But first we should double-check that the Orders table is ok
DBCC CHECKTABLE ('[dbo].[Orders]') WITH ALL_ERRORMSGS, NO_INFOMSGS
[Orders] is good.
Set database in single user mode to enable repair
ALTER DATABASE [CorruptionChallenge4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
The corrupt table is dbo.Customer
Run repair of dbo.Customer
DBCC CheckTable('dbo.Customers', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
EXEC sp_changedbowner 'sa' GO -- This sometimes shows up, but should be dropped if CDC is off DISABLE TRIGGER [tr_MScdc_ddl_event] ON DATABASE GO DISABLE TRIGGER [noDropTables] ON DATABASE GO DISABLE TRIGGER [noNewTables] ON DATABASE GO EXEC sys.sp_cdc_disable_db GO
Based on the DBCC CheckTable removing all rows from the Customers table, this next section could be skipped.
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_Orders_People] GO ALTER TABLE [dbo].[Customers] DROP CONSTRAINT [PK_Customers] GO TRUNCATE TABLE [dbo].[Customers] ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([id] ASC) ON [UserObjects] GO
Now to put everything back into the Customers table.
SET IDENTITY_INSERT [dbo].[Customers] ON INSERT INTO [dbo].[Customers] ( [id], [FirstName], [MiddleName], [LastName] ) SELECT [id], [FirstName], [MiddleName], [LastName] FROM tempdb.dbo.fff SET IDENTITY_INSERT [dbo].[Customers] OFF
Turn the triggers back on
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_People] FOREIGN KEY ([customerId]) REFERENCES [dbo].[Customers]([id]) GO ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_People] GO EXEC sys.sp_cdc_enable_db GO EXECUTE sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Customers', @role_name = NULL; EXECUTE sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Orders', @role_name = NULL; ENABLE TRIGGER [noDropTables] ON DATABASE GO ENABLE TRIGGER [noNewTables] ON DATABASE GO ALTER DATABASE [CorruptionChallenge4] SET MULTI_USER
Not for the checks.
SELECT * FROM [dbo].[Customers]
SELECT * FROM dbo.Customers WHERE id IN (510900, 510901)
SELECT COUNT(*) FROM sys.objects WHERE is_ms_shipped = 0;
And that wraps it up, that is how Randolph West won the Week 4 Database Corruption Challenge.