Post reply

Generate script to restore log backups

  • July 21, 2014 at 1:21 pm

    #1731189

    SQLRNNR (7/21/2014)

    Jeff Moden (7/21/2014)

    Jack Corbett (7/21/2014)

    SQLRNNR (7/21/2014)

    I am a bit surprised this hasn't been asked yet and it seems REALLY important.

    Why do you have 2000 log backups to restore?

    If you truly have that many log backups to restore, then it seems like it is time to correct your DR scenario.

    That many log backups heightens your risk to error in the event of a disaster.

    +1

    I agree but I also have to say that "It Depends". For example, 2000 log file backups taken at 5 minute intervals covers 10,000 MINUTES. Divide that by 60 and you end up with 166.7 hours, which is just shy of 1 week. On larger databases, it's not uncommon to do a once per week full backup with a week long log file chain. That would allow someone to keep two weeks of backups in an online fashion using only two copies of the large database.

    Heh... of course, if the log backups are taken once an hour, then there's a real live problem with this scenario.

    I've worked on a lot of those larger databases. Even with once a week full backup, why not do a daily diff?

    Granted you still keep the weeks worth of tlog backups around, but this allows you multiple recovery paths and reduces the amount of risk.

    I would much rather have multiple avenues of recovery in a disaster. And in the event that everything works perfect to recover, then I just need 1 full, 1 diff and any tlogs after that diff. 2001 files to locate and restore, or a maximum of 290 files to locate and restore (1 full, 1 diff and 288 tlogs for 5 minute intervals over the course of 24hrs). I like the fewer files route.

    As always, it depends.

    If there are maintenance operations, like rebuild or defrag, then the diff may be as large as a full backup.

    That said, I would want to have the option to be able to restore no more than one day of transaction log backups, so I would like either a daily full or diff if it is possible.

  • July 21, 2014 at 1:27 pm

    #1731192

    Jeff Moden (7/21/2014)

    SQLRNNR (7/21/2014)

    Jeff Moden (7/21/2014)

    Jack Corbett (7/21/2014)

    SQLRNNR (7/21/2014)

    I am a bit surprised this hasn't been asked yet and it seems REALLY important.

    Why do you have 2000 log backups to restore?

    If you truly have that many log backups to restore, then it seems like it is time to correct your DR scenario.

    That many log backups heightens your risk to error in the event of a disaster.

    +1

    I agree but I also have to say that "It Depends". For example, 2000 log file backups taken at 5 minute intervals covers 10,000 MINUTES. Divide that by 60 and you end up with 166.7 hours, which is just shy of 1 week. On larger databases, it's not uncommon to do a once per week full backup with a week long log file chain. That would allow someone to keep two weeks of backups in an online fashion using only two copies of the large database.

    Heh... of course, if the log backups are taken once an hour, then there's a real live problem with this scenario.

    I've worked on a lot of those larger databases. Even with once a week full backup, why not do a daily diff?

    Granted you still keep the weeks worth of tlog backups around, but this allows you multiple recovery paths and reduces the amount of risk.

    I would much rather have multiple avenues of recovery in a disaster. And in the event that everything works perfect to recover, then I just need 1 full, 1 diff and any tlogs after that diff. 2001 files to locate and restore, or a maximum of 290 files to locate and restore (1 full, 1 diff and 288 tlogs for 5 minute intervals over the course of 24hrs). I like the fewer files route.

    Yep... I absolutely agree but it still "Depends". Although hard disk space is "cheap", it's not always available. Doing the DIFs is essentially a duplication of everything in the log files since the last BAK file. Some folks just don't have the space for that.

    "...Doing the DIFs is essentially a duplication of everything in the log files since the last BAK file..."

    I'm sure you really meant that a diff is a duplication of every page in the database that is new or changed since the last full backup, not a duplication of the log files. :ermm:

  • July 21, 2014 at 5:38 pm

    #1731265

    Michael Valentine Jones (7/21/2014)

    I'm sure you really meant that a diff is a duplication of every page in the database that is new or changed since the last full backup, not a duplication of the log files. :ermm:

    Technically and usually actually, yes. But (as you know), if you restore the log files up to the point of the DIF, you end up at the virtually same point. That's a duplication of data albeit usually smaller than what might be in the log files. On the other hand and in theory, if a DB suffered only batch INSERTs and single UPDATEs to any given rows as batches, the DIFs would be a near duplicate of the changes contained in the log files.

    I'm not saying that's a bad thing. Like the others, I agree that having more than one method of recovery is a real Martha Stewart moment... but not everyone has such disk space.

    Getting back to the original problem, if you had 2000 log files over the week and did, say, daily DIFs, would you actually delete the log files covered by the DIFs? I wouldn't. There's nothing wrong with having 2000 log files whether or not you have DIFs or not.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2014 at 6:19 pm

    #1731278

    Jeff Moden (7/21/2014)

    Getting back to the original problem, if you had 2000 log files over the week and did, say, daily DIFs, would you actually delete the log files covered by the DIFs? I wouldn't. There's nothing wrong with having 2000 log files whether or not you have DIFs or not.

    My preference would be that the log files be kept. Keeping the log files allows for an alternate recovery path should a diff become corrupt. It's good to have multiple recovery options.:cool:

    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

  • July 21, 2014 at 9:12 pm

    #1731292

    SQLRNNR (7/21/2014)

    Jeff Moden (7/21/2014)

    Getting back to the original problem, if you had 2000 log files over the week and did, say, daily DIFs, would you actually delete the log files covered by the DIFs? I wouldn't. There's nothing wrong with having 2000 log files whether or not you have DIFs or not.

    My preference would be that the log files be kept. Keeping the log files allows for an alternate recovery path should a diff become corrupt. It's good to have multiple recovery options.:cool:

    Glad to see we're in full agreement on that. Like Yogi Berra used to say, "When you come to a fork in the road, take it". 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 19 (of 19 total)

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