| Author |
Topic |
|
stephe40
Posting Yak Master
218 Posts |
 Posted - 2006-07-20 : 16:11:10
|
| I have a small question about point in time recovery. I can't seem to find a direct answer in the BOL.Consider the following situation.Time/Operationt1 - full backaupt2 - trans log backupt3 - trans log backupt4 - trans log backupt5 - full backupWith this sequence, a point in time recovery at t4.5 (between t4 and t5) would not be possible... correct? In order for that to be possible we would need to do a transaction log backup first at point t5, and then do the full backup immediatly after. Right?t6 - trans log backupt7 - trans log backupt8 - trans log backup / full backupWith the additional sequence of events... If you completly lose the full backup that happend at t5, is a point in time recovery possible at t6.5 (between t6 and t7)? I always thought it would be possible by just restoring the full backup at t1 and rolling forward the logs, ignoring the fact a full backup happened in between. But I am unable to find any documentation that confirms my assumption.- Eric |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-20 : 16:20:47
|
quote:
With this sequence, a point in time recovery at t4.5 (between t4 and t5) would not be possible... correct? In order for that to be possible we would need to do a transaction log backup first at point t5, and then do the full backup immediatly after. Right?
No. The full backups that occur in between the transaction log chain do not impact the chain. So you could do a full backup at t5 and then a tlog at t6. You would still be able to restore to a point in time at t4.5. You would just use the full backup at t1 plus the tlog chains up to and including t5. quote:
With the additional sequence of events... If you completly lose the full backup that happend at t5, is a point in time recovery possible at t6.5 (between t6 and t7)? I always thought it would be possible by just restoring the full backup at t1 and rolling forward the logs, ignoring the fact a full backup happened in between. But I am unable to find any documentation that confirms my assumption.
You just need some full backup then the tlog chain after that full backup to the point in time. So you could have performed a full backup 2 years ago and still have the entire tlog chain up to today. You could still do a restore to a point in time of today. Even if you had full backups in between, you could still use that old full backup. Fulls just allow you to apply less tlogs in the case of a point in time recovery.Tara Kizer |
 |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2006-07-20 : 16:27:03
|
quote:
Originally posted by tkizerNo. The full backups that occur in between the transaction log chain do not impact the chain. So you could do a full backup at t5 and then a tlog at t6. You would still be able to restore to a point in time at t4.5. You would just use the full backup at t1 plus the tlog chains up to and including t5.
Do you mean up to and including t6, since no transaction log backup happend at time t5? And if t6 didn't happen yet, then a point in time recovery at t4.5 would not be possible right?- Eric |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-20 : 16:32:06
|
| You just need a transaction log backup that includes the time that you want to restore. Then you need a full backup plus the entire chain up to this tlog. So it doesn't matter which full backup you choose. You just need the entire tlog including the one that has the time. You typically select a recent full backup so that you don't have to restore too many tlogs.If the tlog hasn't occurred that includes the time, then it isn't possible to do the restore yet. But you could just do a tlog backup and now it'll include it.Tara Kizer |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2006-07-20 : 16:35:53
|
| Yep, I understand now, thanks.- Eric |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2006-07-20 : 16:37:43
|
| I guess I was just confused by this BOL statement:Note During a full database or differential backup, Microsoft® SQL Server™ backs up enough of the transaction log to produce a consistent database for when the database is restored.- Eric |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-20 : 16:42:05
|
quote:
Originally posted by stephe40
I guess I was just confused by this BOL statement:Note During a full database or differential backup, Microsoft® SQL Server™ backs up enough of the transaction log to produce a consistent database for when the database is restored.- Eric
I don't understand that statement easier. My knowledge of the backups is based upon emergency situations (there were many of these at my last job) and testing. The basic idea though is you need a full backup and entire tlog chain up to the time. We do full backups daily and tlog backups every 15 minutes. So we potentially could need quite a few tlogs to do a point in time restore. I believe Kristen has a script that makes this easy.Tara Kizer |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2006-07-20 : 16:56:46
|
| Currently we have daily full backups and a script in place that checks all the tranaction logs on a server every 5 minutes, and if any of them are more than 30% full, it initiates tlog a backup.We are considering adding more logic to this job to also do a backup if there has been more than 4 hours since the last tlog backup.- Eric |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-20 : 17:00:54
|
| We are unable to lose more than 15 minutes of data on most of our systems due to the criticality of it to our customers, so that's why we require so frequent backups. The amount of data that you are willing to lose in case you lost the database and can't perform the final tlog backup is what will determine how often you need to back it up. We do have a few systems that can lose an entire days worth of work since the data can be recreated from a different source. On these types of systems, we only perform full backups and use simple recovery model.Tara Kizer |
|
|
|
|
Point in time recovery