T-SQL query to get the latest available backup chain

  • Brahmanand Shukla

    SSChasing Mays

    Points: 660

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

  • Jeff Moden

    SSC Guru

    Points: 994858

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ScottPletcher

    SSC Guru

    Points: 98168

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 994858

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

Viewing 4 posts - 1 through 4 (of 4 total)

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