May 14, 2010 at 2:01 pm
Some friend called me today with the following question:
He has a large production SQL server 2005 OLTP database and makes one full backup weekly and one differential backup every evening. I don't know how, his database was set for full recovery mode and he truncated the log daily after the differential backup as the TL (naturally) was growing and growing (can you believe it?). In fact, the full recovery mode and taking regular transaction log backups is the correct approach fir him, but actually he never did a log backup.
The case is that due to a SAN failure the MDF file became corrupted at filesystem level (irrecuperable in other words) but the transaction log since the last differential backup is optimal.
Under these circunstances... is there anyway to restore the last differential backup and then "apply" the transaction log to achieve the recovery to the point of failure???
Thanks in advance,
Phill
May 14, 2010 at 2:06 pm
No.
The key fact here is this:
his database was set for full recovery mode and he truncated the log daily after the differential backup as the TL (naturally) was growing and growing
By truncating the log he broke the log chain. Once broken, a log backup cannot be taken until another full/diff backup is taken. In fact, once the log is truncated, SQL behaves as though the DB is in simple recovery until the log chain is restarted by a full/diff backup. The best he can do is to restore to the last diff backup and accept that all data since then is lost.
I would advice that you explain to your friend how to correctly manage the transaction logs, including log backups. This may help. Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2010 at 2:38 pm
Thanks Gail.... That's exactly what I told him, anyway I posted the question considering that someone could have some tool or trick or "non documented" method to 'apply' a TL to the database having in consideration that log contains all the transactions since last differential backup....
May 14, 2010 at 2:56 pm
FelixG (5/14/2010)
having in consideration that log contains all the transactions since last differential backup....
but it won't.
If he had not truncated the log, it would, and then it would be possible to take a log backup and apply that after restoring the diff. However in this case, because he explicitly truncated the log, he told SQL that he was not interested in maintaining a log chain and, after that log truncation, SQL would behave as if it were in simple recovery - auto-truncating the log and marking the inactive portions as available for reuse whenever a checkpoint occurs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2010 at 3:16 pm
indeed, the order of execution of the log truncation _after_ the diff backup causes the log to be unusefull for recovery purposes.
Maybe a log analyser tool can still recover (script) undo / redo stuff, but there will not be a guarantee you wouldn't be missing activity data.
As Gail stated, restore the previous full backup (norecovery) and the latest diff backup (with recovery)
and handle the data loss !
It's a hard lesson. Let's hope it can save the future.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply