January 12, 2012 at 2:12 pm
I have looked at several posts on the web, but I am still not completely satisfied with the information I gathered on this question, so here it is.
Suppose I have a full database backup running at the same time as several transaction-log backups of the same database.
Here is the timeline:
10 pm: full backup starts - fb
10:15 pm: log backup 1 starts - lb1
10:35 pm: log backup 1 ends - lb1
10:45 pm: log backup 2 starts - lb2
11 pm: full backup ends - fb
11:05 pm: log backup 2 ends - lb2
Now suppose I am in a DR situation and I want to recover this database up to the time point captured by backup lb2.
Do I restore in the following order?
(1) Restore database from fb with norecovery
(2) Restore log from lb2 with recovery
Does it matter that lb2 had started while the full backup was still running?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 12, 2012 at 3:28 pm
Marios Philippopoulos (1/12/2012)
(1) Restore database from fb with norecovery(2) Restore log from lb2 with recovery
Does it matter that lb2 had started while the full backup was still running?
You have to restore lb1 between step 1 and 2 and no, it doesn't matter if lb2 has started while the full backup was still running (except for deferred log truncation => see paragraph Clear those logs here http://technet.microsoft.com/en-us/magazine/hh334997.aspx).
January 12, 2012 at 6:04 pm
azdzn (1/12/2012)
Marios Philippopoulos (1/12/2012)
(1) Restore database from fb with norecovery(2) Restore log from lb2 with recovery
Does it matter that lb2 had started while the full backup was still running?
You have to restore lb1 between step 1 and 2 and no, it doesn't matter if lb2 has started while the full backup was still running (except for deferred log truncation => see paragraph Clear those logs here http://technet.microsoft.com/en-us/magazine/hh334997.aspx).
Thank you.
What happens if a previous log backup, lb0, had started at 9:45 pm (ie. prior to start of the full backup) and completed at 10:05 pm (ie. after full backup started). Should it also be included in the recovery sequence as step 2, ie. first log restore after fb restore?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 12, 2012 at 10:12 pm
Marios Philippopoulos (1/12/2012)
azdzn (1/12/2012)
Marios Philippopoulos (1/12/2012)
(1) Restore database from fb with norecovery(2) Restore log from lb2 with recovery
Does it matter that lb2 had started while the full backup was still running?
You have to restore lb1 between step 1 and 2 and no, it doesn't matter if lb2 has started while the full backup was still running (except for deferred log truncation => see paragraph Clear those logs here http://technet.microsoft.com/en-us/magazine/hh334997.aspx).
Thank you.
What happens if a previous log backup, lb0, had started at 9:45 pm (ie. prior to start of the full backup) and completed at 10:05 pm (ie. after full backup started). Should it also be included in the recovery sequence as step 2, ie. first log restore after fb restore?
Marios,
My understanding of backups is that you would only need the TX Log backups from AFTER the full backup. I honestly don't think you could restore the TX Log backup that started before the Full Backup started (lb0) because the full backup is going to have a later LSN than the one for log backup.
Check out these 2 posts by Paul Randal:
I know there is one out there that directly addresses what you are asking but I couldn't find it tonight. It might even be by Gail Shaw
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 14, 2012 at 6:34 am
10 pm: full backup starts - fb
10:15 pm: log backup 1 starts - lb1
10:35 pm: log backup 1 ends - lb1
10:45 pm: log backup 2 starts - lb2
11 pm: full backup ends - fb
11:05 pm: log backup 2 ends - lb2
You should follow given restore sequence...
•Restore FB(10 pm)
•Restore lb1
•Restore lb2 (PIT)
•Recovery
January 14, 2012 at 6:51 am
Marios Philippopoulos (1/12/2012)
What happens if a previous log backup, lb0, had started at 9:45 pm (ie. prior to start of the full backup) and completed at 10:05 pm (ie. after full backup started). Should it also be included in the recovery sequence as step 2, ie. first log restore after fb restore?
Maybe, but probably not in the scenario you gave with the full ending at 11 am.
Seriously it depends on what phase the backup was in at 10:05. There's no harm in restoring it. If it's not needed, SQL will say so (something about the log being too early for this database) and you can just continue with the next one. If it is needed and you don't restore it, none of the further log backups will restore.
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
January 15, 2012 at 5:31 am
GilaMonster (1/14/2012)
Marios Philippopoulos (1/12/2012)
What happens if a previous log backup, lb0, had started at 9:45 pm (ie. prior to start of the full backup) and completed at 10:05 pm (ie. after full backup started). Should it also be included in the recovery sequence as step 2, ie. first log restore after fb restore?Maybe, but probably not in the scenario you gave with the full ending at 11 am.
Seriously it depends on what phase the backup was in at 10:05. There's no harm in restoring it. If it's not needed, SQL will say so (something about the log being too early for this database) and you can just continue with the next one. If it is needed and you don't restore it, none of the further log backups will restore.
I guess my fear is that due to some freak timing between the log and full backups, the log sequence may get "corrupted" or unrecoverable. But I'm sure the people at the SQL server backups team would have thought of all these possibilities.:-)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 15, 2012 at 7:17 am
Marios Philippopoulos (1/15/2012)
I guess my fear is that due to some freak timing between the log and full backups, the log sequence may get "corrupted" or unrecoverable.
Unless you're doing stupid things like switching to simple recovery model part way through the backup, or backup log ... truncate only (2005) or backup log ... to nul, or somehow deleting the log file itself, no.
Full backups do not truncate the log, they do not mess with the log backup sequence. That's why you can just ignore a full backup and restore logs right the way through the time of the full backup (diffs are the same)
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply