Automated restore using Ola Hallengren’s SQL Server Backup

script it…walk away.

Greetings humans. This is the second time I’m writing this post because of some wordpress trickery, so pardon me if I make it short. I’m still finishing up part 2 of my first Simple Talk article, while making sure I’m ready for two more presentations, all while making sure I’m ready for a musical performance May 1st. So things are a little busy 🙂

Have you ever wanted to generate all the commands you need to make a restore in SQL Server? Greg Robidoux has a solution for that. Have you ever wanted to do the same thing while using Ola Hallengren’s SQL Server Backup solution? Jason Carter has a solution for that.

Have you ever wanted to have a stored procedure that can do the restores, and also allow the point in time option? @_dbassassin has a solution for that! Recently I wanted a way to restore our production databases to our staging server, in an automated way, and we didn’t want to fork out more money for more licenses of Red Gate SQL Backup. Since all our servers that do not use RedGate utilize Ola Hallengren’s scripts, Jason Carter’s solution was a great start. My modifications include:

1. It’s now a stored procedure. This makes it great for running inside sp_MsForEachDB.

2. It can execute the commands for you! That’s right kids, you can now schedule automatic restores.

3. It has an option for point in time restores! Yes indeed. If you perform a tail backup, you can execute the procedure and perform a point in time restore.

4. If no point in time is specified, the script will just restore to the latest log backup.

5. A couple debug levels exist, some for troubleshooting, some for just printing the commands and one for executing them.

I’ve tested this in a few specific environments, all running SQL Server 2012 and 2014. The script is free and open for suggestion. If you want to mod it and make it cooler, then that’s sweet, do me a favor and let me know so I can check it out. There is a disclaimer that basically says, I’m not responsible for anything bad that happens. If you accidentally run this on your production server when you shouldn’t…damn. If you break anything at all by using this script…damn.

Currently I’ve found two things for the todo list:

1. Add the ability to call the tail backup from Ola Hallengren’s script. You can get an example job here and hopefully next week if I get some free time I will just add in the sqlcmd portion and feed it the database name variable.

2. Add the ability to perform the lastest full backup, and or the latest diff backup without logs. Although I’m not sure this is exceptionally helpful, some people might find it useful (thinking about environments with really large full and diff backups).

Let me know if you have any questions or suggestions

Cheers

6 comments

  1. Hi Tim,

    Nice script, just what i’m after.
    However is there any chance you could make this support SQL 2005; I’m receiving the following errors…

    Msg 139, Level 15, State 1, Procedure sp_automate_restore, Line 0
    Cannot assign a default value to a local variable.
    Msg 137, Level 15, State 2, Procedure sp_automate_restore, Line 112
    Must declare the scalar variable “@Path”.

    Many Thanks,
    Edd

  2. I found an issue where the location is in a directory with spaces in it. You need to change the following line to read as follows:

    SET @cmd = ‘DIR /b “‘ + @backupPath + ‘”‘

    Other than that, nice job!

  3. I dont see any link to download or see the script?
    How can I get Jason Carter script with your additions?
    Regards

  4. Late to the party, but this script is extremely helpful, thank you! I have been perusing many scripts for restoring Hallengren backups, but yours does exactly what I was looking for.

    I made three changes to my copy of your script.

    1) I added [SnapshotUrl] NVARCHAR(360) at the end of the @Table declaration, as RESTORE FILELIST in SQL 2016 and later returns this additional column.

    2) I fixed a bug that was retrieving the incorrect time values from the @PointInTime variables, changing
    SET @DateTimeValue = CONVERT(VARCHAR, CONVERT(DATETIME, SUBSTRING(@PointInTime,
    1, 8)), 111)
    SUBSTRING(@PointInTime, 8, 2)
    + ‘:’ + SUBSTRING(@PointInTime, 10, 2)
    + ‘:’ + SUBSTRING(@PointInTime, 12, 2);

    to

    …SUBSTRING(@PointInTime, 9, 2)
    + ‘:’ + SUBSTRING(@PointInTime, 11, 2)
    + ‘:’ + SUBSTRING(@PointInTime, 13, 2);

    3) Additionally, in initializing the backupsFiles CURSOR, I added ISNULL to return @lfb if @ldb is NULL, which was true in my case, as I was not making differential backups.

    DECLARE backupFiles CURSOR
    FOR
    SELECT backupFile
    FROM @fileList
    WHERE backupFile LIKE ‘%_LOG_%’
    AND backupFile LIKE ‘%’ + @backupDBName + ‘%’
    AND REPLACE(LEFT(RIGHT(backupFile, 19), 15), ‘_’, ”) > (ISNULL(@ldb, @lfb))
    ORDER BY backupFile;

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.