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!
This was my plan as well but I couldn’t get it to work. When I went to set the database back online with the original LDF (SQL statement #3), I got this error:
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file ‘R:\SQLLogs\CorruptionChallenge3_log.LDF’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
What am I missing?
Dave,
If you replace just the CorruptionChallenge3_log.LDF file you will get the message you mentioned. If you delete the CorruptionChallenge3.MDF file you get a totally different message, however with that MDF gone, the LDF loads enough so that you can run the tail of the log backup.
-Steve
Thanks for the challenge and the solution.
To get the solution by myself is out of my knowledge at this point but I do enjoy working through the given solutions.
What tripped me up when running the above solution is when the database was put back ONLINE errors show up in the SSMS Results pane.
This made me think something was not right and second guess myself because there was no indication that anything was actually ONLINE.
I suppose my brain was thinking “how can a database come online when their is no .mdf” so was I really meant to remove it?
Although a refresh in Object Explorer and the ability to run further scripts did prove otherwise but not after a bit of confusion.
I got this message:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\CorruptionChallenge3\sqlfiles\CorruptionChallenge3.mdf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.
Msg 945, Level 14, State 2, Line 1
Database ‘CorruptionChallenge3’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
thanks again.
For anyone unsure of using this approach then it is covered in a blog Post by Paul Randal – http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/