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
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
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!
I dont see any link to download or see the script?
How can I get Jason Carter script with your additions?
Regards
https://github.com/dbassassin/simpletalk
there is a branch waiting to be merged…from two years ago, i just haven’t had the time to test the new changes.
How can I get the Jason Carter script with your additions.
http://www.codepimp.org/2015/04/automated-restore-using-ola-hallengrens-sql-server-backup/#comment-14145
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;