Home Dashboard Directory Help
Search

Add ability to recover data from LDF when tail-log backup not available by RecycledDBA


Status: 

Closed
 as Won't Fix Help for as Won't Fix


0
1
Sign in
to vote
Type: Suggestion
ID: 490471
Opened: 9/17/2009 10:20:10 AM
Access Restriction: Public
0
Workaround(s)
view

Description

Example situation:
Full recovery model
Daily full backups
Hourly log backups

The server crashes and therefore you cannot perform a tail-log backup. As a result you will lose up to one hour of data.

Since LDF contains all transactions that occurred since last log backup, why can't we recover data from the LDF file?

Currently only option to ensure zero data loss would be to implement database mirroring or to some extent clustering.
Details
Sign in to post a comment.
Posted by RecycledDBA on 9/22/2009 at 10:34 AM
Very interesting idea and I completely agree that this plan is sufficient. I will add that bit of information to my recovery plans.
Posted by Microsoft on 9/22/2009 at 10:15 AM
While it would be ideal to make every possible scenario simple and straightforward to do, reality is that we need to prioritize where we put our efforts.

In this case, the chances of having BOTH the instance and the MDF being unavailable, but having the LDF be completely intact seem somewhat remote. Possible, but not likely.

It is possible to recover all of your data in this case (see below). It's not straightforward, but given the rarity of the situation, I'm making the call that it's sufficient.

In order to recover all data, you would need to:
1. restore system databases or re-install SQL to get the instance back running.
2. Copy the surviving log file off to a safe location to prevent it from being overwritten by the restores.
3. Restore the database from full backup
4. Restore any needed log backups
5. Bring the database online.
6. Stop the server.
7. Replace the log file with the original one which you copied in setp 2.
8. Start the instance. The database will come up in Suspect state because the log file doesn't match.
9. Perform a tail-log backup on this log file to obtain the last of your data.
10... Start over with restoring the database, this time including the final tail-log backup. This will bring you up to the time of the failure.

As I said, it's not pretty, but it is possible. (I personally validated that it works)
Posted by RecycledDBA on 9/21/2009 at 1:14 PM
I was under the assumption that the 'continue_after_error' was a last resort effort with possibility of corrupted data. After re-reading about it and it does state it will only work on logs if the log files are undamaged so data should be good.

Recovery options:
1) Cannot access SQL Server engine: If I can still get to MDF then I can simple recover from that.
2) Corrupted or missing MDF: If I can still access SQL Server then perform tail-log backup with "continue_after_errors" option. Restore from backups.

Proposed option:
3) Cannot access SQL Server or MDF: Restore database from backups and recover tail-log information from original LDF.

As I stated in comments below, SQL Server already has solid recovery process and I can understand recovering from LDF would not make sense if it requires re-write of log file process. But logically if the data is already there and in format that can be recovered, why not offer option to use it. In the end my guess is that this is something that sounds simple but extremely complex to undertake.

Thank you for the feedback on the recovery option.
Posted by RecycledDBA on 9/21/2009 at 1:05 PM
Yes, you are correct, I meant to indicate that you cannot get the MDF or it is corrupted and the only option is to restore from backups. Since log files should be on a different volume in which you may still be able to access the LDF. Based on the "continue_after_error" option that MS pointed out, this would have to be a double whammy... cannot access MDF and cannot access SQL Server to perform tail-log backup. For example if the binaries and default data directory existed on the same volume... bad configuration but I'm sure it happens.

Thought came from research into MySQL for a project to move the db's to SQL Server. The idea of recovering directly from log file sounded like a good idea and would represent an additional recovering option. SQL Server already has solid recovery process and I can understand recovering from LDF would not make sense if it requires re-write of log file process. But logically if the data is already there and in format that can be recovered, why not offer option to use it. In the end my guess is that this is something that sounds simple but extremely complex to undertake.
Posted by Microsoft on 9/21/2009 at 10:09 AM
To be clear, Tail Log backups do not require that the database be up and running.
You can specify WITH CONTINUE_AFTER_ERROR to get past the errors caused by the rest of the database being unavailable.

This is the method implemented to retrieve data from the LDF file of a database which is damaged past the ability to bring it online.
Posted by Adam Machanic on 9/20/2009 at 1:38 PM
It's not clear from your scenario why you wouldn't be able to do your final backup. You still have the LDF, apparently; are you saying that in the scenario as listed you've lost the MDF, but not the LDF?
Sign in to post a workaround.