Post reply

Generate restore script from disk for Ola Hallengren backups

  • February 24, 2016 at 4:46 pm

    #399769

    Recently I've came across a post or a blog that referenced a great utility script by Greg Robidoux for generating a restore script for a database based on the contents of a directory. I tried it, and it worked great, so long as all your backups are in the same directory and had a good naming convention. Shortly afterward I started implementing Ola Hallengren's maintenance script to perform backups and maintenance, and found the script didn't work for those servers, but Jason Carter had made some changes and posted it - it didn't work either. I'm not a blogger, but I learn a lot from the community, and want to give back, so I'm sharing a script I've updated that have tested against backups taken using Ola's maintenance solution.

    I hope this helps someone out there, and please let me know if you find bugs - I haven't tested it against anything other than SQL 2014 right now, but will once I have more time.

    Reason for change:

    Ola's script uses a .BAK extension for differentials, and stores FULL, DIFF and LOG backups in a sub-folder heirarchy that matches @backupPath\@@SERVERNAME\@dbName\[FULL|DIFF|LOG]\ and the filename also contains what type of backup file it is within the filename, making filename comparison for order of restore impossible.

    ChangeLog:

    2/24/2016 - Joe O'Connor (thirtybird@gmail.com)

    Allow backup paths to have spaces in them by encapsulating path in the command in quotes

    Fixed DIR command - /O D to /O:D to guarantee order by date to ensure transaction logs are restored in proper order

    Added wrapper to enable and disable xp_cmdshell and re-set the "show advanced options" setting to whatever it was

    If you have xp_cmdshell enabled in your environment, take this part out or it will get disabled!

    Took out extra @backupPath in each RESTORE command - the full path is output into the file list and was duplicated in the output

    Added backupTime to the local table variable to be used for comparison as comparing filenames doesn't work with Ola's filenames

    This involves substring parsing (and is ugly).

    2/25/2016 - Joe O'Connor (thirtybird@gmail.com)

    Added logic to check to see if xp_cmdshell needs to be enabled or not. Only enables and disables if it needs to.

    Verified it does not disable xp_cmdshell if it was enabled to start with.

  • February 24, 2016 at 8:22 pm

    #1860955

    Thanks for taking the time to "give back". Ola would probably love to hear about your improvements. You might want to stop by his site and drop the same nickel to him.

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

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