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:

http://stevestedman.com/server-health/database-corruption-challenge/week-4-database-corruption-challenge/week-4-challenge-details/

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;