Unusual Backup Strategy

  • 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.

  • 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/


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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



    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • 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)

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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