Automating restores for Ola Hallengren backup solution

While going through Brent Ozar’s 6-Month DBA Training Plan, I got myself a good challenge quite fast, after reading “Automating Restores” – to automate restores on backups taken using Ola Hallengren Backup Solution.
After reading the email and being pointed to MSQLTips’s script – a great script and post on how to automate restores, my starting point, i wanted to enhance the script so it can do the following:

  1. Handle Ola Hallengren backup folder structure
  2. Have the option to restore the database with a different name
  3. Create the folders needed for database files if they don’t exist

Before anything, these scripts use xp_cmdshell procedure, so it needs to be enabled:

Here’s my demo database i’ve created, and assured it’s in FULL recovery mode.

And I already ran Ola’s script to create the stored procedures used for backups.
Using them, I’ve took some *random* backups:

With these backups taken, I expect to have a restore sequence for backups taken at lines 3,6,7,8.

Now the code:
I need to provide database name I want to restore as @dbName, optionally, the new database name I want to be restored as – @newDbName and the root backup folder used for stored procedure to take backups as @backupPath.

I got all backup files created and stored the into a table, with fileName and backupType:

And this is what it gets:

Next, find the latest FULL backup:

If I have a new name for database to be restored as, using RESTORE FILELISTONLY, I’m getting the details about the physical location of the files. I would want the new file names to be renamed if they are using their database name in them, or if there’s a dedicated folder for this database I would want to create a new dedicated folder, or if the same file name is used, on the exact same path, I want to make sure the file name is not the same with the original one.

And add the MOVE statement for each file:

Now, for each new location where the new files should be placed, I need to check it exists, and if not, create the needed folders:

The rest of the script remains somehow the same as the one on MSQLTips. Find the latest differential backup, get all transaction log backups since last differential backup and add them to the script. Lastly, RESTORE WITH RECOVERY:

I have put this into a stored procedure available to download below and here are a few uses:

1. When trying to restore a database with a different name:

which produces:

2. Trying to restore the database with the same name, replacing the current one:

which produces:

You can download the stored procedure here.
You can find here MSQLTips’s script and for Ola Hallengren’s backup scripts please visit his website.

This was quite a good challenge for me following Brent Ozar’s 6-Month DBA Training Plan – Automating Restores read and turned out to be quite handy.
I hope you enjoyed the long read.

  1. This is a nice tool, thanks for sharing. One thing I’d change, though …
    One lines 10-14, your code evaluates backup type:

    UPDATE @fileList SET
    backupType = CASE WHEN CHARINDEX(‘FULL’, backupFile) > 0 THEN ‘FULL’
    WHEN CHARINDEX(‘DIFF’, backupFile) > 0 THEN ‘DIFF’
    WHEN CHARINDEX(‘LOG’, backupFile) > 0 THEN ‘LOG’

    I have a database named LOGGER, which causes trouble in the evaluation. It’s in simple recovery but contains the string “LOG” in the filename. I propose that your check include the backslashes around the directory names, making your test look like:

    UPDATE @fileList SET
    backupType = CASE WHEN CHARINDEX(‘\FULL\’, backupFile) > 0 THEN ‘FULL’
    WHEN CHARINDEX(‘\DIFF\’, backupFile) > 0 THEN ‘DIFF’
    WHEN CHARINDEX(‘\LOG\’, backupFile) > 0 THEN ‘LOG’

    Since Ola’s backup folder structure uses these folder names, this change ensures that database names containing strings like FULL, DIFF, and LOG can be processed accurately.

  2. That’s a very good point, thank you Steve. I will update the script reflecting your comments.

  3. Lakshmi Saripalli

    Hi Horia,
    Just wanted to bring to your notice a small typo in the stored procedure that you put up for download. The charindex search string should be ‘\FUll\’ and you have it as ‘/FULL/’. This is preventing from populating the @fileslist table with the right column data.

    Thank you very much for this script. I need my prod server backups that I do using Ola’s scripts and need to restore them onto a different test server on a weekly basis. DO you have any pointers on how I can do that?

  4. You’re right, i have fixed that – thank you Lakshmi.

    As for your question, i have something similar in place. All my backups get shipped to a shared drive (azure) which is accessible from all my environments. Then, every night i restore the backups on different server to make sure backups are reliable (i only do this for FULL + DIFF as I have log shipping in place).
    Hope that gives you some ideas.

  5. Lakshmi Saripalli

    Thank you Horia for the response. I do the same. Write all backups to Network share accessible by all environments. I also have a proc that will generate auto restore scripts that i can use in case of DR.The trouble I’m having is being able to restore from a prod to a test server(and automating this). I am not that good at code writing..

    So far I have tweaked your process, also passing the server name as a variable and now I can do the restore for one db onto another server. but doing the same for multiple db’s may be the next challenge.

    If you do have a easier way please let me know.

  6. Love the script. how can it be modified to use a unc path for backups like \\backups\share ?? Thanks In advande.

    • Hi Alex, sorry for late reply. Script should work with UNC paths too, if the account under which Sql Server is running (or Sql Agent if you’re running the script from a job) has permissions to access the share. Do you have any problems with that?

Leave a Comment