Comments (28)

  1. Hi,
    I’d love to hear your opinion on a procedure I’ve just developed that does the same as your script. It includes a STOPAT option which can be used to construct restore scripts based on historic backups/diffs/logs, IE, not necessarily the most recent full.

    Best wishes

    1. Great script, Paul. One of the things I was trying to demonstrate in this script was basing the association between the different types of backups on the appropriate LSN value. I think that’s safer than basing it only on date.

  2. Your script has shown me the way really, I’ll add a check of the LSN.
    Thanks and regards

  3. I gave the script a test run, but every database I ran it against that is in the full recovery model I received the following error: Msg 530, Level 16, State 1, Line 151
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


    1. That’s a lot of log backups per database, but after thinking about it, if you back up the log every 5 minutes, you could reach that in a little more than 8 hours. I hadn’t thought of that. Thanks for bringing this up. I’ll add the MaxRecursion hint to the recursive CTE.

      1. I think you added the hint to the wrong query.

        1. Thanks Wayne! You are right. I did get it right in the actual script that’s attached, but I seem to have trouble finding the right place inside the HTML of the post.

  4. Thats an excellent script Robert,really helpful.

  5. My procedure has been changed to use the backup LSN’s as you do, your join condition to backup media set was also added. You are credited at the bottom , the article and script have been submitted to sql server central. Your series of posts on DR planning are great and very useful.

    1. Cool! Thanks for giving me credit! I look forward to seeing it posted.

  6. If you run this against a fresh 2012 install against a system database, is shows a backup for a virtual device, but this is null in the output (the case statement doesn’t consider device_type = 7). And, I don’t think that these can be restored in any case (aren’t they just like backing up to the NUL device?), so would you want to exclude this device type from the queries?

    1. Thanks Wayne. Yes, I think those should be excluded.

  7. Fantastic script! Thank you very much for sharing your knowledge. It is greatly appreciated.

  8. Quick note: The virtual backup devices are usually third party tools backing up your databases. When things like backup exec backup SQL Server they call VDI that generates a virtual device and assigns it a GUID for the device name.


Leave a Reply

Your email address will not be published. Required fields are marked *