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.
- It would be helpful if the return contained the actual restore commands for the latest backup chain.
- 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?
- 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.
- 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.