Database Corruption Week 6 Solution

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

Check for corruption in the database, isolate to non clustered index.
Keep changes that will be lost when repairing damage. In this case there are 2 row first name values that “look” to be incorrect in the cluster. Write two simple updates to be run following disable and rebuild of non clustered index – cross checking with business that the values are indeed corrupt. Then run final check script.

USE [master]
RESTORE DATABASE [CorruptionChallenge6] FROM  DISK =  
N'C:\SQL_Data\CorruptionChallenge6.bak' WITH  FILE = 1,  NOUNLOAD,   
STATS = 5,REPLACE
GO
USE [CorruptionChallenge6]
GO
;WITH
c AS(
        SELECT [id],[FirstName],[LastName] FROM [dbo].[Customers] WITH
(INDEX([PK_Customers]))
),
nc AS(
        SELECT [id],[FirstName],[LastName] FROM [dbo].[Customers] WITH
(INDEX([ncFName]))
)
SELECT * FROM c FULL JOIN nc ON c.[id] = nc.[id] WHERE c.[FirstName] <> nc.[FirstName] OR c.[LastName] <> nc.[LastName]
--id        FirstName        LastName        id        FirstName        LastName
--252971        €ea        MULLEN        252971        Mia        MULLEN
--992        Aliwander        FISCHER        992        Alexander        FISCHER
GO
ALTER INDEX [ncFName] ON [dbo].[Customers] DISABLE 
GO
ALTER INDEX [ncFName] ON [dbo].[Customers] REBUILD
GO
DBCC CHECKDB('CorruptionChallenge6') WITH NO_INFOMSGS,ALL_ERRORMSGS
GO
SELECT COUNT(1) AS check1,
        COUNT(DISTINCT FirstName) AS check2,
        COUNT(DISTINCT MiddleName) AS check3,
        COUNT(DISTINCT LastName) AS check4,
        CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
        CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
        CHECKSUM_AGG(CHECKSUM(LastName)) AS check7 FROM [dbo].[Customers];
--check1        check2        check3        check4        check5        check6        check7
--254031        19        17        879        -8736600        179        1330080960
SELECT * FROM [dbo].[Customers] WHERE [id] IN (992,252971)--Aliwander/€ea
GO
UPDATE [dbo].[Customers] SET [FirstName] = 'Mia' WHERE [id] = 252971
UPDATE [dbo].[Customers] SET [FirstName] = 'Alexander' WHERE [id] = 992
GO
SELECT COUNT(1) AS check1,
        COUNT(DISTINCT FirstName) AS check2,
        COUNT(DISTINCT MiddleName) AS check3,
        COUNT(DISTINCT LastName) AS check4,
        CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
        CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
        CHECKSUM_AGG(CHECKSUM(LastName)) AS check7 FROM [dbo].[Customers];

--check1        check2        check3        check4        check5        check6        check7
--254031        17        17        879        1046491532        179        1330080960

SELECT * FROM [dbo].[Customers] WHERE [id] IN (992,252971)

Now the problem is that corruption has been corrected with what we think should be in the clustered index, based on a hunch that the data looks better. The page verify option should have caught this corruption as it is set to page checksum.

But this corruption also rewrote the page checksum ;-p

--Alternative Fix for corruption in clustered index, then fix nc as before. DO NOT USE DBCC WRITEPAGE in PRODUCTION environments!!! It is very dangerous.
/*
--FOR TEST ENVIRONMENTS ONLY--
DBCC PAGE ([CorruptionChallenge6],1,174,3) WITH TABLERESULTS
DBCC PAGE ([CorruptionChallenge6],1,1215,3) WITH TABLERESULTS
ALTER DATABASE [CorruptionChallenge6] SET SINGLE_USER
DBCC WRITEPAGE (N'CorruptionChallenge6',1,174,896,2,0x6578,0);--Byte 1426304-1426305 69 77 to 65 78
DBCC WRITEPAGE (N'CorruptionChallenge6',1,1215,3488,2,0x4D69,0);--Byte 9956768-9956769 80 65 to 4D 69
ALTER DATABASE [CorruptionChallenge6] SET MULTI_USER
*/