Database Corruption Week 7 Solution

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

1. Review Transaction log and find point at which rows were lost.

SELECT * FROM fn_dblog(NULL,NULL) lf

Transaction Name Current LSN
DELETE 00000025:00000270:0001 deleted by 0x01 (maps to sa) spid 51 at 2015/05/31  
17:57:56:287

2. Backup live transaction log/Restart Transaction log job

BACKUP LOG [CorruptionChallenge7] TO  DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_6.trn'

3. Restore alternative database [CorruptionChallenge7Recovery]

USE [master]
RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\CorruptionChallenge7_1.bak'
WITH NORECOVERY,
MOVE N'CorruptionChallenge7' TO  
N'C:\SQL_DATA\CorruptionChallenge7Recovery.mdf',
MOVE N'UserObjects' TO N'C:\SQL_DATA\UserObjectsRecovery.ndf',
MOVE N'CorruptionChallenge7_log' TO  
N'C:\SQL_DATA\CorruptionChallenge7Recovery.ldf'--,REPLACE

RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_0.trn' WITH NORECOVERY
RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_1.trn' WITH NORECOVERY
--RESTORE HEADERONLY FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_2.trn' WITH NOUNLOAD;--not  
needed
--RESTORE HEADERONLY FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_3.trn' WITH NOUNLOAD;--not  
needed
--RESTORE HEADERONLY FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_4.trn' WITH NOUNLOAD;--not  
needed
RESTORE DATABASE [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_5.trn' WITH NORECOVERY
RESTORE LOG [CorruptionChallenge7Recovery] FROM DISK =  
N'C:\SQL_Data\TransLog_CorruptionChallenge7_6.trn'
WITH STOPATMARK  = 'lsn:0x00000025:00000270:0001'
--WITH STOPATMARK  = 'lsn:37:624:1'
GO

4. copy rows from recovery to live database if happy

SELECT COUNT([Record1000Id]) as check1
        ,CHECKSUM_AGG(CHECKSUM([FireDeptID])) as check2
        ,CHECKSUM_AGG(CHECKSUM([FireDeptState])) as check3
        ,CHECKSUM_AGG(CHECKSUM([AlarmDate])) as check4
        ,CHECKSUM_AGG(CHECKSUM([IncidentNumber])) as check5
        ,CHECKSUM_AGG(CHECKSUM([ExposureNumberZeroBased])) as check6
        ,CHECKSUM_AGG(CHECKSUM([RecordType])) as check7
        ,CHECKSUM_AGG(CHECKSUM([TransactionType])) as check8
        ,CHECKSUM_AGG(CHECKSUM([FireDepartmentStation])) as check9
FROM [OPEN_NFIRS].[Record1000];

check1        check2        check3        check4        check5        check6        check7        check8        check9
3010        2346        0        1643050        26216226        0        0        0        246

SET IDENTITY_INSERT [CorruptionChallenge7].[OPEN_NFIRS].[Record1000] ON
INSERT INTO [CorruptionChallenge7].[OPEN_NFIRS].[Record1000]  
(Record1000Id, FireDeptID, FireDeptState, AlarmDate, IncidentNumber,  
ExposureNumberZeroBased, RecordType, TransactionType,  
FireDepartmentStation)
SELECT Record1000Id, FireDeptID, FireDeptState, AlarmDate,  
IncidentNumber, ExposureNumberZeroBased, RecordType, TransactionType,  
FireDepartmentStation FROM  
[CorruptionChallenge7Recovery].[OPEN_NFIRS].[Record1000]
SET IDENTITY_INSERT [CorruptionChallenge7].[OPEN_NFIRS].[Record1000] OFF