June 17, 2011 at 4:27 am
Since no one actually verified this for him:
hammad772001 (6/16/2011)
So it means that we can only mirror the backup copies of the Transaction Logs, but not the LDF files themselves. So in case MDF and LDF is missing (assuming due to a disk crash), log backups were taken 1 hour before the crash, then we can only retrieve the data until the last log backup by restoring the database backup and applying the last log backup on it. All the transactions after the last log backup are lost.Please correct me if I'm wrong.
You are correct, assuming the backups are all valid and uncorrupted and the log backups are associated with the Full DB backup that you are restoring.
Remember, though, that just because the dates on the backups look sequential, it doesn't mean they are all associated. If someone broke your log chain by doing a full backup (instead of a copy backup) and moved that full backup elsewhere, then the transaction logs after your full backup might not be related to your backup at all.
Gail has an excellent blog about it here: http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/[/url]
June 17, 2011 at 4:37 am
Brandie, a full backup doesn't break the log chain, although a lost or corrupted log backup certainly would.
John
June 17, 2011 at 4:40 am
And the blog post that Brandie listed explains that and what copy_only backups are really for.
Log backups are only based on the previous log backup, not fulls or diffs. The only time they're based on a full or a diff is when that is the first log backup after the log chain was re-established (after a switch to simple recovery or explicit truncation of the log)
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
June 17, 2011 at 5:04 am
John,
Sorry. You are correct. A full backup doesn't usually break the log chain.
I suppose I should have just said "chain" without the "log" because I've seen people do screwy things that make it impossible for them to restore what looks like sequential backups (but really aren't). This happens because a lot of shops don't have good control over their backups. They stuff files from different instances in the same directory, mix up copies from secondary & primary servers, etc.
It's scary what people do (or don't do) with their backup files. And when you have two different departments fighting over who's responsible (server team vs dbas), it gets even messier.
If you want examples, I'll post them, but I'm trying not to take over the thread.
June 17, 2011 at 5:07 am
John Mitchell-245523 (6/17/2011)
Brandie, a full backup doesn't break the log chain, although a lost or corrupted log backup certainly would.
Oh, yes. And finding that bit of information out when you desperately need to restore is the worst time to realize that sequential date doesn't mean anything. @=)
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply