Database Corruption Week 2 Solution

Unfortunately I missed week 2 submissions, but here is my solution for week two. The original database and details for this can be found here:

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

The data corruption is confined to a single page. Page 244. There is a non clustered index available meaning that the only columns that are an issue are [Year] and [Notes], which are retrieved from the old backup file and cross checked with the contents of the corrupt page.

USE [master]
RESTORE DATABASE [CorruptionChallenge2] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\2\CorruptionChallenge2_LatestBackup.bak' 
WITH  FILE = 1, 
MOVE N'CorruptionChallenge2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2.mdf',  
MOVE N'CorruptionChallenge2_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2_log.LDF'
GO
USE [master]
RESTORE DATABASE [CorruptionChallenge2_TwoDaysAgoBackup] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\2\CorruptionChallenge2_TwoDaysAgoBackup.bak' 
WITH  FILE = 1, 
MOVE N'CorruptionChallenge2' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2_TwoDaysAgoBackup.mdf',  
MOVE N'CorruptionChallenge2_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CorruptionChallenge2_TwoDaysAgoBackup.LDF'
GO

USE [CorruptionChallenge2];
WITH 
a AS(SELECT TOP (595) * FROM [dbo].[Revenue] ORDER BY [ID] ASC),
b AS(SELECT TOP (9450) * FROM [dbo].[Revenue] ORDER BY [ID] DESC),
Good AS(SELECT *,1 [RecordStatus] FROM a UNION ALL SELECT *,1 [RecordStatus] FROM b),
PartialGood AS(
	SELECT p.[ID],p.[DepartmentID],p.[Revenue],Old.[Year],Old.[Notes],0 [RecordStatus] FROM [CorruptionChallenge2].[dbo].[Revenue] p WITH (INDEX([ncDeptIdYear]))
	LEFT JOIN [CorruptionChallenge2_TwoDaysAgoBackup].[dbo].[Revenue] Old
	ON  p.[ID] = Old.[Id]
	WHERE NOT EXISTS (SELECT * FROM [Good] WHERE [Good].[ID] = p.[ID])
)

SELECT * INTO [dbo].[RevenueLatest] FROM Good UNION ALL SELECT * FROM PartialGood
GO
SELECT * FROM  [dbo].[RevenueLatest] WHERE [RecordStatus] = 0--These records were rebuilt from data held in an old backup
DBCC PAGE('CorruptionChallenge2',1,244,1) WITH TABLERESULTS--This can be crossed checked against the damage page. It is only 12 rows, so we can check manually.
GO
TRUNCATE TABLE [Revenue] 
GO
DBCC CHECKDB('CorruptionChallenge2') WITH NO_INFOMSGS,ALL_ERRORMSGS;
GO
SET IDENTITY_INSERT [dbo].[Revenue] ON;
INSERT INTO [Revenue] ([id],[DepartmentID],[Revenue],[Year],[Notes])
SELECT [id],[DepartmentID],[Revenue],[Year],[Notes] FROM [dbo].[RevenueLatest]
SET IDENTITY_INSERT [dbo].[Revenue] OFF;
GO
EXEC [dbo].[checkCorruptionChallenge2Result]
GO