May 28, 2013 at 7:33 am
Hi,
One of our clients (whose SQL Server we don't support) has an unusual backup strategy within SQL Server.
They appear to be taking a Full SQL Backup once per week, and then each night taking a Transaction Log backup.
In essence, they appear to be using Transaction Log backups in place of Differential backups.
The people that support their SQL Server have apparently said that they do this for all their clients and are happy with the process
Is this a common setup as I've never seen it before?
Normally, I would recommend Differential Backups with regular transaction log backups throughout the day.
Is there any advantage to using Transaction Log Backups, rather than Differential Backups?
Also, are there any major drawbacks to performing backups this way so that I can advise the client?
Obviously, one of the main benefits of a differential backup is the ease of restoring in the case of a failure, but I can't think of any reason why using Transaction Log backups is particularly bad... it just doesn't feel like it's the right way to do it.
Thank you for your help.
May 28, 2013 at 8:23 am
Transaction log backups are scheduled based on how much data loss you can tolerate
If the transactions log backup is scheduled every night, it implies that they can tolerate data loss up to 24 hours
If they can't tolerate that much data loss they have to schedule T-LOG backups at a much higher frequency
You can have a look at the below given article which will give you more idea
http://www.sqlservercentral.com/articles/Administration/64582/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 28, 2013 at 8:29 am
I wouldn't have said this was normal at all and certainly wouldn't be practical for us. Our t-log file would grow to be unmanageable if our backups were like this.
As Kingston says, at the end of the day it comes down to how much data they're prepared to lose if a disaster occurs.
Thanks,
Simon
May 28, 2013 at 9:30 am
Hi,
Thanks for the replies.
I appreciate that it all comes down to the amount of data loss that is acceptable.
I suppose the bit that I'm struggling to understand is why they feel the benefit of overnight Transaction Log backups, rather than overnight Differential backups.
Both seem to provide the same safety cushion (i.e. up to 24 hours data loss)
However, the Differential backups would be a lot easier to restore (i.e. just 1 differential backup, rather than x amount of Transaction Log backups)
May 28, 2013 at 10:03 am
Sounds like the database is using either full or bulk_logged recovery model. The nightly t-log backup will clear the transaction log where as a differential would not.
May 28, 2013 at 10:08 am
Steve Smith-163358 (5/28/2013)
Is this a common setup as I've never seen it before?
No.
Is there any advantage to using Transaction Log Backups, rather than Differential Backups?
No
Also, are there any major drawbacks to performing backups this way so that I can advise the client?
Yes. Overly large log files, since they need to hold 24 hours of logs, and up to 24 hours of data loss in the case of a disaster. The interval between log backups should be decided based on the allowable data loss in the case of a disaster.
If they really are fine with 24 hours of data loss (which their current setup allows), then perhaps simple recovery and daily diffs will be a better idea as the log won't need to be so large.
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
May 28, 2013 at 10:16 am
Thank you all for your replies.
It confirms exactly what I thought.
My hands are tied a bit as someone else provides support for their SQL Server, but now that I'm confident I'm not missing something, I will get in touch with them.
Thank you
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply