October 19, 2010 at 8:57 am
I have a pretty good script put together that backs up my transaction log at 15 minute intervals. I also create a nightly full database backup.
Are Differential db backups necessary to restore from trans logs backups (point of failure)? Or can I just use the Full db backup to restore and then restore all the transaction log backups created since the last full backup.
One method I read about is something to the effect of - Create one full db backup per week, then daily differential backups along with transaction log backups throughout that week.
This method seems risky due to the fact that you could have a corrupt differential db backup or transaction log backup and the entire chain could be broken.
Advice, thoughts?
October 19, 2010 at 9:47 am
Are Differential db backups necessary to restore from trans logs backups (point of failure)? Or can I just use the Full db backup to restore and then restore all the transaction log backups created since the last full backup.
You are right. Differential backups are not necessary to restore transaction log backup files.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 19, 2010 at 10:10 am
My prefered methodology is to do regular transaction log backups like you're doing, a full backup at night, a differential at noon, then overwrite the transaction log with the first log backup at the start of business hours (assuming banker's hours, not 24/7). This also assumes the server is backed up during the night.
The advantage of the differential is to reduce the number of transaction logs to be restored in certain circumstances. If you're backing up every 15 minutes starting at 8am, you'll have 17 backups at noon. Assuming your server crashes at 12:05pm and you have to restore, with a differential at noon you restore the full, the diff, and you're back to 12:00. Without the diff, you restore the full and 17 transaction log backups. You can extrapolate what happens if it dies at 4:55pm.
Of course, the restore dialog makes this very easy to do whichever way you go and automaticaly selects and restores the appropriate logs, in my experience it's a lot faster to restore the incremental.
I do not like the one full backup a week, it totally goes against my experience and the risk of one backup not being captured then a total server failure and rebuild from scratch. Of course, it totally depends on your environment and the size of the databases that you're dealing with.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 19, 2010 at 11:59 am
SkyBox (10/19/2010)
Are Differential db backups necessary to restore from trans logs backups (point of failure)? Or can I just use the Full db backup to restore and then restore all the transaction log backups created since the last full backup.
No, they're generally used to shorten the restore time. It's quicker to restore full, diff and then logs that day than full and all log backups that week. Also depends on yur available backup windows. If you don't have time to take a full backup daily, then diff may be a good idea
This method seems risky due to the fact that you could have a corrupt differential db backup or transaction log backup and the entire chain could be broken.
Corrupt diff won't hurt as long as you have the previous (uncorrupt) diff and the log backups since that one. Diff backups don't break the log chain. A corrupt log backup will kill any restore path, regardless of what backups you have.
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
October 19, 2010 at 1:22 pm
Of course, the restore dialog makes this very easy to do whichever way you go and automaticaly selects and restores the appropriate logs, in my experience it's a lot faster to restore the incremental.
Let's say - I do the restore through the GUI and I have my own scripted naming conventions on all the back up files..............will the Gui automatically recognize these?
When you say "incremental", are you referring to "differential"? I remember reading that these are 2 different back up types. Just want to be sure that I am understanding you correctly.
Thank you
October 19, 2010 at 1:44 pm
Thanks for the great explanations everyone. I am beginning to put it all together now, but this raises more questions for my particular environment.
My call center enters sales orders 24/7, so I am trying to piece together the perfect backup schedule. My production db is about 500gb. The Transaction log is about 40gb. I have plenty of drive space so space should not be an issue.
Here is my proposed schedule:
- Full db backup nightly at 2am (completes around 3:30am).
- Differential db backup 3 times a day (8am, 2pm, 8pm,).
- Transaction log backups every 15min beginning at 4am and ending at 1:45am the following morning before the full back up.
What do you all think? I am eager to set up the jobs and test the restores.
October 19, 2010 at 2:02 pm
you are in aright track.
But it would be better if you take differential every one hour and and set up clean up task to delete old differential backups so that in case of system failure
you need restore only one full backup --> one diffrential --> four tran log backups.
Thats it ...other wise you need to restore plenty tran backups since last differential backups.
October 19, 2010 at 2:08 pm
SkyBox (10/19/2010)
Thanks for the great explanations everyone. I am beginning to put it all together now, but this raises more questions for my particular environment.My call center enters sales orders 24/7, so I am trying to piece together the perfect backup schedule. My production db is about 500gb. The Transaction log is about 40gb. I have plenty of drive space so space should not be an issue.
Here is my proposed schedule:
- Full db backup nightly at 2am (completes around 3:30am).
- Differential db backup 3 times a day (8am, 2pm, 8pm,).
- Transaction log backups every 15min beginning at 4am and ending at 1:45am the following morning before the full back up.
What do you all think? I am eager to set up the jobs and test the restores.
Couple of comments:
1.The transaction log backups should be set to run every 15 minutes, 24x7. People often forget about important processes, like reindexing that are scheduled to run overnight, so just let them run around the clock. Don't worry, they will not cause a problem with the full backups.
2. If you are running SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 Standard or Enterprise Edition, you should look at using compressed backups. Thye will probably be faster and use less disk space.
3. I doubt that the Differential backups will really be that useful, but they won't hurt.
October 19, 2010 at 2:36 pm
Couple of comments:
1.The transaction log backups should be set to run every 15 minutes, 24x7. People often forget about important processes, like reindexing that are scheduled to run overnight, so just let them run around the clock. Don't worry, they will not cause a problem with the full backups.
2. If you are running SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 Standard or Enterprise Edition, you should look at using compressed backups. Thye will probably be faster and use less disk space.
3. I doubt that the Differential backups will really be that useful, but they won't hurt.
1. Wondered about the conflict; noted - thanks
2. Running 2008 Enterprise. Have considered the compressed backups, but would have to test thoroughly before put into production. I can count on my full backups because I use them in testing and dev. environments all the time. I definitely plan on doing this in the future.
3. My understanding is that the differentials would save me some work/time because I would not have to restore as many transLog backups. If I had to restore at 10pm without the diffs, I would have to restore all the transaction log backups (80) since the last full db backup, in order recover to the point of failure. Give or take 15 minutes...
Thank you - appreciate the comments
October 19, 2010 at 3:19 pm
If you need 24/7 uptime with a database of that size, you should definitely explore mirroring nd other high availability techniques. Do you have a server with enough storage space that you could restore the 500 gig to in order to get the system back up?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 20, 2010 at 10:05 am
Wayne West (10/19/2010)
If you need 24/7 uptime with a database of that size, you should definitely explore mirroring nd other high availability techniques. Do you have a server with enough storage space that you could restore the 500 gig to in order to get the system back up?
I have a clustered fail-over set up for the database servers. The 2 database servers are identical. The drives are on a SAN and I have 2.6tb of drive space allocated for the database clustered drive.
If the SAN went down, I do have a testing/dev server that I could use.
Has anyone had any first hand experience with losing any data on a SAN system?
October 20, 2010 at 10:57 am
SkyBox (10/20/2010)
Has anyone had any first hand experience with losing any data on a SAN system?
Yes.
It's not data loss that Wayne's suggesting mirroring for. It's downtime. Sure, you could restore to the dev/test instance and point the apps at that server, but is the time that is required to do that acceptable to the end user?
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
October 20, 2010 at 11:11 am
We've never had a data loss with our SAN, we have had disks failed and replaced, but that's never resulted in downtime. Our previous NAS box died bigtime, but it was old and pretty much only used for archived backups that were also backed up elsewhere. It couldn't host databases, so there was no risk of production loss.
One thing that's really cool about our SAN is that with dual controllers, we can take down one controller during off-hours, update the firmware, bring it back up, and it will take down the backup controller and update it by itself.
The only quirk about our SAN that I'm aware of was when a volume got filled, the management system refused to recognize when we cleaned it up and brought available storage below 50%, it still thought it was full and continued issuing alerts. I think we eventually got a patch that cleared that up.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 20, 2010 at 11:12 am
GilaMonster (10/20/2010)
SkyBox (10/20/2010)
Has anyone had any first hand experience with losing any data on a SAN system?Yes.
It's not data loss that Wayne's suggesting mirroring for. It's downtime. Sure, you could restore to the dev/test instance and point the apps at that server, but is the time that is required to do that acceptable to the end user?
Thank you, Gail. Yes, that was my point. I've been very short of sleep this last week, we got our house re-roofed and it's been kinda chaotic. 😉
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 20, 2010 at 12:46 pm
Thank you Eights and GilaMonster. Now I understand - never considered the down time to restore...........have been fortunate, SO FAR. This is definitely something that I need to consider.
I am fairly new to this site, but have already learned a lot. You are all so helpful and knowledgeable. Really appreciate all the advice!
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply