Database Corruption Week 4 Solution
Here is my late solution for week four. Blame the UK Bank Holiday. The original database and details for this can be found here:
This week was harder, but easier with the clue provided pointing you towards the right area.
USE [master] RESTORE DATABASE [CorruptionChallenge4] FROM DISK = N'C:\Path\CorruptionChallenge4_Corrupt.bak' WITH FILE = 1, MOVE N'CorruptionChallenge4' TO N'C:\Path\CorruptionChallenge4.mdf', MOVE N'UserObjects' TO N'C:\Path\CorruptionChallenge4_UserObjects.ndf', MOVE N'CorruptionChallenge4_log' TO N'C:\Path\CorruptionChallenge4_log.ldf', NOUNLOAD, STATS = 5 , KEEP_CDC; GO USE [CorruptionChallenge4]; WITH f AS ( SELECT [id],[FirstName] FROM [dbo].[Customers] WITH (INDEX([ncCustomerFirstname]))--(511740 row(s) affected) ), l AS ( SELECT [id],[LastName] FROM [dbo].[Customers] WITH (INDEX([ncCustomerLastname]))--(511740 row(s) affected) ) SELECT f.[id],f.[FirstName],c.MiddleName,l.[LastName] INTO [tempdb].[dbo].[Customers] FROM f INNER JOIN l ON l.[id] = f.[id] INNER JOIN cdc.fn_cdc_get_net_changes_dbo_Customers(sys.fn_cdc_get_min_lsn('dbo_Customers'),sys.fn_cdc_get_max_lsn(),'all') c ON c.[id] = f.[id] --SELECT * FROM cdc.captured_columns --SELECT * FROM cdc.change_tables GO ALTER DATABASE [CorruptionChallenge4] SET SINGLE_USER GO DBCC CHECKTABLE ('[dbo].[Customers]',repair_allow_data_loss)--This will deallocate the pages and leave [dbo].[Customers] empty, which will cause constraint violations. GO DBCC CHECKDB('CorruptionChallenge4') WITH NO_INFOMSGS,ALL_ERRORMSGS; GO DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS GO ALTER TABLE [dbo].[Orders] WITH CHECK CHECK CONSTRAINT ALL--The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Orders_People". The conflict occurred in database "CorruptionChallenge4", table "dbo.Customers", column 'id'. GO SET IDENTITY_INSERT [dbo].[Customers] ON INSERT INTO [dbo].[Customers] ([id],[FirstName],[MiddleName],[LastName]) SELECT [id],[FirstName],[MiddleName],[LastName] FROM [tempdb].[dbo].[Customers] SET IDENTITY_INSERT [dbo].[Customers] OFF GO ALTER TABLE [dbo].[Orders] WITH CHECK CHECK CONSTRAINT ALL GO ALTER DATABASE [CorruptionChallenge4] SET MULTI_USER GO SELECT * FROM [dbo].[Customers] WHERE [id] IN (510900,510901) --id FirstName MiddleName LastName --510900 Steve M Stedman --510901 William V STARK GO SELECT COUNT(*)--9 FROM sys.objects WHERE is_ms_shipped = 0;
Leave a Reply