August 27, 2015 at 5:05 pm
Hi. I have some questions about restoring from backups.
Givens:
Full backup on Sunday (Full) - 11pm
Differential on Monday (D1) - 11pm
Differential on Tuesday (D2) - 11pm
Differential on Wed (D3) - 11pm
Differential on Thur (D4) - 11pm
Transaction Log every hour each day (L1 - L24)
Now:
If I have a failure on Wed at 8:15... Restore Path is
Restore Full
Restore D1
Restore D2
Restore D3
Restore L1 - L8
or is it
Restore Full
Restore D3
Restore L1 - L8
What happens if D3 is corrupt?
Any help on this would be great.
Thanks,
Mike
August 27, 2015 at 8:02 pm
mike 57299 (8/27/2015)
or is it
Restore Full
Restore D3
Restore L1 - L8
Yes. That's it.
What happens if D3 is corrupt?
Restore FULL
Restore D2
Restore L1-L24 for D2
Restore L1-L8 for D3
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2015 at 9:20 pm
Thank you for the info.
In your opinion, which is better - full every night or full once a week and differentials?
Mike
August 28, 2015 at 12:04 am
Hi Mike,
Below is the better backup strategy:
- Full backup every night
- Differential every 6 hours.
- T-log backups every 15 mins. or (30 mins.)
This backup strategy is easier to manage and reduces restore time. Combined that with transaction log backups for better recoverability.
I hope this helps.
Thanks.
Best Regards,
Ricky
August 28, 2015 at 7:49 am
mike 57299 (8/27/2015)
Thank you for the info.In your opinion, which is better - full every night or full once a week and differentials?
Mike
Short answer, it depends.
Long answer, it depends on several things. Will your nightly fulls be able to complete in a reasonable amount of time?
Seeing as the differentials go back to the last full, are the later diffs getting bigger than you'd like?
Will you have sufficient storage to keep a week or more worth of fulls?
Do you or your team take backups of the DBs to restore for testing / development / whatever to other servers? If so, do you always remember to make them copy-only so as to not mess up the differential base? (in which case daily fulls might not be a bad idea, but if you do diffs during the day you'll still need to remember to copy-only them.)
As some background, my current backup system is:
Sunday Full
Monday, Tuesday Differential
Wednesday Full
Friday Differential
M-F Transaction Log every 2hrs
That works for my systems, and my RPO / RTOs, but it may not work for you and yours.
Jason
August 28, 2015 at 9:34 am
If your database has a lot of modifications you may want to push that log backup time down to 15 mins or half hour.
Acceptable data loss window.
Yes. Those are great guide question in implementing a backup strategy.
I would just like to add the following questions as well.
- does your database has a lot of modifications?
- what is you acceptable data loss window.
For example:
if you backup transaction logs every 2 hours,
4pm - tlog backup
6pm - tlog backup
and if something happens to your database (e.g. DB crash, hardware failure) at around 5pm,
then you would lose 1 hour worth of data because you don't have tlog backups in between 4pm to 6pm.
However, if backup t-log every 15 mins, and something happens to your DB at 5:02pm, you will
be able to restore your t-log backup until 5pm so you only have 2 minutes of data loss.
I hope this helps.
Good luck with your backup strategy.
Best Regards,
Ricky
August 28, 2015 at 10:29 am
Ricky, you are correct about basing the TLog backups on how much data loss is acceptable.
In my case, the actual data loss customers agree too (not my call) is up to a *DAY* as we only promise once a day full / log backups. Leaving aside what that infrequent of a backup does to the TLog, *I* wasn't happy with it and modified the schedule.
Also in my case, our DBs are not frequently altered, they're much more read heavy with moderate to little insert / update / deletes, so two hours data loss is not a crisis.
September 3, 2015 at 7:42 pm
jasona.work (8/28/2015)
Ricky, you are correct about basing the TLog backups on how much data loss is acceptable.In my case, the actual data loss customers agree too (not my call) is up to a *DAY* as we only promise once a day full / log backups. Leaving aside what that infrequent of a backup does to the TLog, *I* wasn't happy with it and modified the schedule.
Also in my case, our DBs are not frequently altered, they're much more read heavy with moderate to little insert / update / deletes, so two hours data loss is not a crisis.
I work in the banking, mortgage, and insurance industry. No loss of data is acceptable. The good part about that is that it's absolutely clear to everyone in the company and makes sure that we have to tools and the talent to make it so.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2015 at 7:45 pm
jasona.work (8/28/2015)
mike 57299 (8/27/2015)
Thank you for the info.In your opinion, which is better - full every night or full once a week and differentials?
Mike
Short answer, it depends.
Long answer, it depends on several things. Will your nightly fulls be able to complete in a reasonable amount of time?
Seeing as the differentials go back to the last full, are the later diffs getting bigger than you'd like?
Will you have sufficient storage to keep a week or more worth of fulls?
Do you or your team take backups of the DBs to restore for testing / development / whatever to other servers? If so, do you always remember to make them copy-only so as to not mess up the differential base? (in which case daily fulls might not be a bad idea, but if you do diffs during the day you'll still need to remember to copy-only them.)
As some background, my current backup system is:
Sunday Full
Monday, Tuesday Differential
Wednesday Full
Friday Differential
M-F Transaction Log every 2hrs
That works for my systems, and my RPO / RTOs, but it may not work for you and yours.
Jason
You don't actually have to do a full backup of a large database in one swell foop. 😛 With a little creativity with files/file groups and, perhaps, some partitioning here and there, you can spread out the "full" backups on huge databases over several nights. Keep them log files rolling...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2015 at 10:02 pm
The way I do it is to take a full backup every day. The log backups are taken every 30 minutes. Than again, I don't have any hugs databases where a full can't complete in the time I have. I also have enough disk space to keep 5 days worth of full backups and the associated log files.
If you have enormous databases that you can't do a full backup on, I encourage you to explore what Jeff mentioned with the file groups and partitions. Of course, for the partitions, you need Enterprise Edition. Jeff has done a presentation on partitioning that's truly awesome. He really gets into how things work. If you get the chance to attend a SQL Saturday where he's giving that presentation, it's well worth the time and brain power to attend.
Whatever backup scenario you implement, let me stress the importance of testing your backups by restoring them to somewhere. If you don't test them, you'll never know if they're viable until you really need them. That's too late to learn that they're no good. The only way to be sure is to test. I don't know who said it originally, but I've heard the phrase "You don't need a backup strategy. You need a recover strategy." This shift the focus from the backups to recovery. Yes, the backups have to be in place, but you also have to know they're good.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply