Week 3 – Corruption Challenge – The Winning Solution

Find out more about the Week 3 Challenge on the overview blog post.

The winning solution, was submitted by Randolph West with the following steps:

I created a dummy database with the same name, set it offline, deleted the files, dropped in your LDF, brought it back online, and did a tail of the log backup with NO_TRUNCATE to bypass the error.

Then I followed a standard full restore with NORECOVERY, the three transaction logs you supplied, plus my tail, and set WITH RECOVERY to bring it into my shiny 2008 R2 instance.

CREATE DATABASE [CorruptionChallenge3];
GO

ALTER DATABASE [CorruptionChallenge3]
SET OFFLINE;
GO

-- delete all files and replace with your LDF

-- online
ALTER DATABASE [CorruptionChallenge3]
SET ONLINE;

-- Tail of the log
BACKUP LOG [CorruptionChallenge3]
TO DISK = N'C:\Temp\Corrupt3\CorruptionChallenge3_Tail.trn'
WITH INIT, NO_TRUNCATE;
GO

-- restore
USE [master];

RESTORE DATABASE [CorruptionChallenge3]
FROM DISK = N'C:\Temp\Corrupt3\CorruptionChallenge3_Full.bak'
WITH FILE = 1,
MOVE N'CorruptionChallenge3'
TO N'D:\SQLData\Data\CorruptionChallenge3.mdf',
MOVE N'CorruptionChallenge3_log'
TO N'D:\SQLData\Data\CorruptionChallenge3_log.LDF',
NORECOVERY,
NOUNLOAD,
REPLACE,
STATS = 5;
GO

RESTORE LOG [CorruptionChallenge3]
FROM DISK = N'C:\Temp\Corrupt3\TransLog_CorruptionChallenge30.trn'
WITH NORECOVERY;
GO

RESTORE LOG [CorruptionChallenge3]
FROM DISK = N'C:\Temp\Corrupt3\TransLog_CorruptionChallenge31.trn'
WITH NORECOVERY;
GO

RESTORE LOG [CorruptionChallenge3]
FROM DISK = N'C:\Temp\Corrupt3\TransLog_CorruptionChallenge32.trn'
WITH NORECOVERY;
GO

RESTORE LOG [CorruptionChallenge3]
FROM DISK = N'C:\Temp\Corrupt3\CorruptionChallenge3_Tail.trn'
WITH NORECOVERY;
GO

RESTORE DATABASE [CorruptionChallenge3]
WITH RECOVERY;
GO

The final solution involved bringing in the final 3 rows in the table from the tail of the log backup. Without the tail of the log backup, there would only be 13 rows.

With the tail of the log backup, it takes the remaining transactions in the LDF file. This tail of the log backup can be done even though the MDF file is not existent.

I congratulate Randolph West on winning this weeks event. I job well done. Randolph, I hope to see you in the competition next week.

Links

• Overall Scores
• Week 3 Corruption Challenge
• Week 3 Results
• Database Corruption Challenge (previous weeks)
• Newsletter Signup

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!