Is this a good backup strategy?

  • Hi,

    I would appreciate any feedback on my backup strategy. I also have a question or three. Note that there is little or no activity between 22:00 and 07:00 (SQLServer 2008 R2 web edition). I keep all the files for 70 hours.

    Full Backup - daily at 01:00

    Differential Backups - every 3 hours, from 7:00 to 22:00

    Transaction Log backups - every hour, from 7:30 to 22:30

    Question 1:

    If disaster happens at 12:45, I have to restore the last full backup, then the last differential backup, then the last transaction log backups, right? So, after I restore the full backup and the differential backup from 10:00, should I run log backups from 9:30, 10:30, 11:30 and 12:30? Does SQL recognize that the first half hour from the 9:30 log, is already back in place because the 10:00 differential backup was restored?

    Question 2:

    Using "Maintenance Plans", I can check "Back up the tail of the log, and leave the database in the restoring state". Beats me, should I check this or not?

    Question 3:

    Is there a good book on Backup and Restoring?

    Thanks!

    Ray

  • The real answer is simple : does it work? That implies you must have requirements and tested that you can restore the files (which I do on all my backups automatically anyways).

    What are your DR requirements and where did you fail to restore data in your tests?

    Tail log should only be used when you retire a db / server and move it to a new location. It leaves the db in restoring state meaning you can't use it.

  • Raymond van Laake (5/3/2011)


    If disaster happens at 12:45, I have to restore the last full backup, then the last differential backup, then the last transaction log backups, right?

    Last full, last diff, all log backups since the diff.

    Question 2:

    Using "Maintenance Plans", I can check "Back up the tail of the log, and leave the database in the restoring state". Beats me, should I check this or not?

    Do you want to leave the database in a restoring state where no one can read or connect to it? If so, sure, check that.

    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
  • Hi,

    Thanks for your replies.

    Do you want to leave the database in a restoring state where no one can read or connect to it? If so, sure, check that.

    Clear, thx!

    Last full, last diff, all log backups since the diff.

    Agreed, but does SQL recognize that the first half hour from the 9:30 transaction log, is already back in place because the 10:00 differential backup was restored just before?

  • Raymond van Laake (5/3/2011)


    Agreed, but does SQL recognize that the first half hour from the 9:30 transaction log, is already back in place because the 10:00 differential backup was restored just before?

    Ask yourself, do you think that it wouldn't?

    Log backups are based only on the previous log backup, not full or diff (unless something broke the log chain), so no matter what the backup pattern is, when the log backup is restored on top of a full or diff, there will be some transactions recorded in the log backup that are already hardened in the restored database.

    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
  • Why don't you test. That's the only you'll know for sure and understand what's happening.

    You'll sleep better at night once you do.

  • Hehe, you're absolute right about testing! I certainly will, it's just @#$@$@##$@* busy here... but I know I must take the time to do the tests.

    Thanks!

  • Raymond van Laake (5/3/2011)


    Hehe, you're absolute right about testing! I certainly will, it's just @#$@$@##$@* busy here... but I know I must take the time to do the tests.

    Thanks!

    You wanna see busy and stress. Try testing your DR strategy when you already lost the prod server. That gives you a good sweat.

  • Raymond van Laake (5/3/2011)


    Hehe, you're absolute right about testing! I certainly will, it's just @#$@$@##$@* busy here... but I know I must take the time to do the tests.

    Thanks!

    Just imagine how ******* busy it will be when you have to do a restore and you're not sure if it's going to work and you have half the management staff standing in your cube or on a conference call with you at 3AM and you're not sure how to do the point in time recovery because you haven't tried it out in about 6 months so you need to look up a how to article on the web or in books online... At this point in any conversation with your boss, suddenly, there's a little more time around the office.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Just to pile on, an untested backup strategy is not a backup strategy. It's a backup desperate hope.

    I've seen someone cause 4 hours additional downtime during a disaster because he hadn't practised doing restores of full, diff and log, and he restored the diff WITH RECOVERY, resulting in the restore having to be restarted from scratch.

    Needless to say, no one was very impressed.

    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
  • GilaMonster (5/3/2011)


    Just to pile on, an untested backup strategy is not a backup strategy. It's a backup desperate hope.

    I've seen someone cause 4 hours additional downtime during a disaster because he hadn't practised doing restores of full, diff and log, and he restored the diff WITH RECOVERY, resulting in the restore having to be restarted from scratch.

    Needless to say, no one was very impressed.

    The exact reason why I have a script to generate the restore script at point in time. Tested, works like a charm. Sleeping better at night :w00t:

  • Ninja's_RGR'us (5/3/2011)


    GilaMonster (5/3/2011)


    Just to pile on, an untested backup strategy is not a backup strategy. It's a backup desperate hope.

    I've seen someone cause 4 hours additional downtime during a disaster because he hadn't practised doing restores of full, diff and log, and he restored the diff WITH RECOVERY, resulting in the restore having to be restarted from scratch.

    Needless to say, no one was very impressed.

    The exact reason why I have a script to generate the restore script at point in time. Tested, works like a charm. Sleeping better at night :w00t:

    Care to share?:Whistling:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (5/3/2011)


    Ninja's_RGR'us (5/3/2011)


    GilaMonster (5/3/2011)


    Just to pile on, an untested backup strategy is not a backup strategy. It's a backup desperate hope.

    I've seen someone cause 4 hours additional downtime during a disaster because he hadn't practised doing restores of full, diff and log, and he restored the diff WITH RECOVERY, resulting in the restore having to be restarted from scratch.

    Needless to say, no one was very impressed.

    The exact reason why I have a script to generate the restore script at point in time. Tested, works like a charm. Sleeping better at night :w00t:

    Care to share?:Whistling:

    Sure, but tomorrow when I have access to it.

    Basic idea is that you can use the GUI to generate that script.

    <Side track>

    I once had an issue that could be resolved by adding indexes in the msdb tables where the restore screen would take 15-30 minutes to load because there was a crapload of history in that table (technical term).

    Anyhow I don't know if this is still an issue in 2K5 but I used that pretext to build another script that does the same thing

    </ Side track>.

    In all my jobs I have a specific structure for the backup names. All the files end with the date/time of the file.

    From there it's just a matter of loading the filenames with cmdshell and figuring out what needs to be restored to satisfy the PIT.

    ATM, the script only deals with fulls and logs since I don't use diffs here.

    The other benefit of the script is that I can now leave log backups at 15 minutes 24/7 even if not realllllly required. Since it doesn't take any more time to generate the script or restore the DB.

    Remind me to post it tommorrow if I forget.

  • Ninja's_RGR'us (5/3/2011)


    SQLRNNR (5/3/2011)


    Ninja's_RGR'us (5/3/2011)


    GilaMonster (5/3/2011)


    Just to pile on, an untested backup strategy is not a backup strategy. It's a backup desperate hope.

    I've seen someone cause 4 hours additional downtime during a disaster because he hadn't practised doing restores of full, diff and log, and he restored the diff WITH RECOVERY, resulting in the restore having to be restarted from scratch.

    Needless to say, no one was very impressed.

    The exact reason why I have a script to generate the restore script at point in time. Tested, works like a charm. Sleeping better at night :w00t:

    Care to share?:Whistling:

    Sure, but tomorrow when I have access to it.

    Basic idea is that you can use the GUI to generate that script.

    <Side track>

    I once had an issue that could be resolved by adding indexes in the msdb tables where the restore screen would take 15-30 minutes to load because there was a crapload of history in that table (technical term).

    Anyhow I don't know if this is still an issue in 2K5 but I used that pretext to build another script that does the same thing

    </ Side track>.

    In all my jobs I have a specific structure for the backup names. All the files end with the date/time of the file.

    From there it's just a matter of loading the filenames with cmdshell and figuring out what needs to be restored to satisfy the PIT.

    ATM, the script only deals with fulls and logs since I don't use diffs here.

    The other benefit of the script is that I can now leave log backups at 15 minutes 24/7 even if not realllllly required. Since it doesn't take any more time to generate the script or restore the DB.

    Remind me to post it tommorrow if I forget.

    Thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Raymond van Laake (5/3/2011)


    Hi,

    I would appreciate any feedback on my backup strategy. I also have a question or three. Note that there is little or no activity between 22:00 and 07:00 (SQLServer 2008 R2 web edition). I keep all the files for 70 hours.

    Full Backup - daily at 01:00

    Differential Backups - every 3 hours, from 7:00 to 22:00

    Transaction Log backups - every hour, from 7:30 to 22:30

    Question 1:

    If disaster happens at 12:45, I have to restore the last full backup, then the last differential backup, then the last transaction log backups, right? So, after I restore the full backup and the differential backup from 10:00, should I run log backups from 9:30, 10:30, 11:30 and 12:30? Does SQL recognize that the first half hour from the 9:30 log, is already back in place because the 10:00 differential backup was restored?

    Question 2:

    Using "Maintenance Plans", I can check "Back up the tail of the log, and leave the database in the restoring state". Beats me, should I check this or not?

    Question 3:

    Is there a good book on Backup and Restoring?

    Thanks!

    Ray

    I would change it to do transaction log backups every 15 minutes, 24x7. It doesn't really cost you extra for performance, and it helps to minimize possible data loss. I personally would not bother with the diff backups, unless there is a large amount of activity, but they won't really hurt.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply