Post reply

T-SQL query to get the latest available backup chain

  • October 30, 2019 at 7:39 am

    #3693611

    Comments posted to this topic are about the item T-SQL query to get the latest available backup chain

    SQL Server Carpenter - Consultant & Trainer
    Author of Learn T-SQL from Scratch

  • November 8, 2019 at 4:13 pm

    #3696297

    This is a nice script and I thank you for taking the time to post it.

    Just a couple of thoughts, though.  Your article states...

    This query can be helpful for knowing the latest backup chain and size of the backups for the capacity planning and also to estimate the space requirement for database restore.

    1. It would be helpful if the return contained the actual restore commands for the latest backup chain.
    2. Why not just query the directory of the backup disk(s) for capacity planning while measuring the total capacity and free space available on the disk?
    3. Estimating the space requirement for database restores from the backup size can be really misleading.  One of the largest problems in this area is that free space in the database isn't backed up either for the data files or the log files, which could be huge compared to what is actually needed.
    4. Another huge problem with the code in general is that the data all comes from MSDB.  In a DR situation or even in just a "let's load it on another machine", that data probably won't be available (especially in a DR situation).

    Now, if you really want to write something useful (think major "Life Saver" for DR situations), write some code where you pass a directory path for where your backups are stored and the code will find the latest FULL backups for all the databases and then not only return a lot of the information you have in your script but also have it trip through all the Dif files and Log file backups to create all of the commands necessary to restore a database (or all the databases) to a point in time.  The hint here to do such a thing is to learn how to read file headers.

    Oh... and you need to be able to do this without using anything but T-SQL.  You shouldn't have to rely on anything else (like SSIS or 3rd party applications) in a DR situation.

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

  • November 8, 2019 at 4:32 pm

    #3696310

    Jeff Moden wrote:

    Estimating the space requirement for database restores from the backup size can be really misleading.  One of the largest problems in this area is that free space in the database isn't backed up either for the data files or the log files, which could be huge compared to what is actually needed.

    The log file will be full size when restored, no matter how much of the log file was actually filled when the backup occurred.  This means, btw, that it can be a good idea to force a log shrink before taking a backup you plan to restore to a remote system, then re-grow the log after the restore, although normally you would typically never shrink a log file below its normal usage size.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • November 8, 2019 at 10:12 pm

    #3696411

    ScottPletcher wrote:

    Jeff Moden wrote:

    Estimating the space requirement for database restores from the backup size can be really misleading.  One of the largest problems in this area is that free space in the database isn't backed up either for the data files or the log files, which could be huge compared to what is actually needed.

    The log file will be full size when restored, no matter how much of the log file was actually filled when the backup occurred.  This means, btw, that it can be a good idea to force a log shrink before taking a backup you plan to restore to a remote system, then re-grow the log after the restore, although normally you would typically never shrink a log file below its normal usage size.

    Exactly!

    --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 4 posts - 1 through 3 (of 3 total)

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