August 7, 2015 at 8:05 am
I can probably dig the answer out of Online Books, etc. but maybe someone could quickly say yes or no and why.
Scenario: log backup 1, log backup 2, full backup 1, log backup 3, log backup 4, full backup 2, log backup 5, crash!
OOPS! full backup 2 is corrupt or lost, but all others are OK.
Can I restore full backup 1, then restore log backups 3, 4, and 5 to not lose "too much" data?
August 7, 2015 at 8:21 am
Short answer: yes. Full database backups and differential database backups do NOT break the transaction log chain.
Also, if your crash did not destroy the transaction log and you can get that database into any recognizable state (even if it in SUSPECT mode or throwing other errors), then you can take another log backup after the crash (called a 'tail-of-the-log backup' or 'tail log backup') you won't lose any data at all. Note: you must take this log backup with the NO_TRUNCATE option. If the server's toast, try getting the database files/disks/whatever-you-can-get attached to another instance so you can grab the tail of the log.
After that you can restore the database using any (non-corrupt) full backup and every t-log backup since that full backup was taken, provided it's an unbroken chain of log backup files.
Scenario: log backup 1, log backup 2, full backup 1, log backup 3, log backup 4, full backup 2, log backup 5, crash!
OOPS! full backup 2 is corrupt or lost, but all others are OK.
Can I restore full backup 1, then restore log backups 3, 4, and 5 to not lose "too much" data?
So, the restore for this would be: Take log backup 6 (with NO_TRUNCATE), restore full 1, restore log backups 3,4,5, 6.
-Eddie
Eddie Wuerch
MCM: SQL
August 7, 2015 at 8:51 am
Everything Eddie said is 100% correct (of course).
One concern that you should have though, if the recovery model were changed at any point, that effectively breaks the log backup chain and you're then dependent on the first full backup after the recovery model change. If that's the backup that was lost... you could be in trouble.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 10, 2015 at 7:09 am
Thank you responders! I will probably increase the time I keep log backups "just in case".
August 10, 2015 at 2:36 pm
"So, the restore for this would be: Take log backup 6 (with NO_TRUNCATE), restore full 1, restore log backups 3,4,5, 6."
I have a question about Eddie's answer...
What about log backups 1 & 2 ?.....I believe those need to be restored as well.
Plz correct me if I am wrong, thnx.
Regards,
SQLisAwe5oMe.
August 10, 2015 at 2:38 pm
I am sorry, I misread the question.
I thought log backup 1 and 2 were taken after full backup 1.....plz disregard my question.
Regards,
SQLisAwe5oMe.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply