question

Sharma avatar image
Sharma asked

How to recover database using log file?

Hi, I am in puzzle regarding log file backup if related database file disk has corrupted. Scenario is given below- MS SQL SERVER 2008 Instance Database name is DB1 with full recovery mode. DB1 data file and log file available on different disks. Database full backup available two day’s back. If any point of time data file disk get fail, will I be able to take log file backup or log file will be useful for recovery purpose.
backup-restorelog-backup
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

5 Answers

·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
As always "it depends" :) I suggest you to search for "tail log backup" which describes the item you're looking for. But in general, it's possible:" 1) make a tail log backup from the "corrupted" database 2) restore (overwrite) the full backup with NO RECOVERY 3) apply the logfiles (with NO RECOVERY) 4) apply the tail log backup (with RECOVERY)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
I do not see it much different from other disaster recovery procedures. Since disk failure is as equal as deleting the Data files, Paul Randal has an excellent [blog][1] on this. This will clear your ambiguity. [1]: http://www.sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-backing-up-the-tail-of-the-log.aspx
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JoelBarry avatar image
JoelBarry Suspended answered
I would suggest you to try third party RecoveryFIX for SQL Database Recovery tool that repair the corrupt MDF files by scanning the corrupt or damaged SQL database and displays all the recovered .MDF files after finishing the scanning process. It is equipped with two scanning modes: Server Mode & Batch Mode. This powerful SQL server recovery tool enables you to restore the recovered SQL data to a currently working SQL database. It supports SQL server 2000, SQL server 2005, SQL server 2008 and SQL server 2008 R2 versions. Thanks
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GrumpyDBA avatar image
GrumpyDBA answered
If possible secure a tail log backup, then begin your restores. First restore the most recent Full NoRecovery any Diffs (Norecovery) if applicable, then you apply all logs (still NoRecovery) backup files up to the point in time when the disaster happened and you then apply the tail log with Recovery.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
Go through this link and read MartinC answer: http://dba.stackexchange.com/questions/10582/how-can-i-recover-data-from-the-ldf-file
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.