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