If you have been playing along at home this month then you know about some of the struggles that I have faced with automating the Red Gate Backup Pro automatically.  Well, with the help of Red Gate and some rewriting of their provided script, I now have a script that will work automagically!  When I say automagically, I mean that I want the script to run forever without any more interference from me in the same manner that our Ola Hallengren backup scripts work.

If you are not familiar, this tool is excellent for many reasons but one of the reasons that we like it is for the simple fact that if a new database is created, it will be maintained automagically.  I do not have to create any maintenance plans or agent jobs each time I add a database to a server.  We have several servers that get new small databases all the time and this is a time saver as well as a life saver.  Now this scripts will do the same, it will iterate through the database names and restore the latest backup set as a new database with the name ‘_Restored’ tacked on the end of the existing name and place the data and log file in a separate directory to avoid overwriting your existing databases.

Do not worry about failure reporting with the job as Red Gate will send you an error for each database, pass or fail.  If you wish to see only the failures then change ‘MAILTO’ to ‘MAILTO_ONERRORONLY’ in the script.

–This script will restore multiple databases where each database has it’s own Full Backup to be restored.
USE master

DECLARE @dbname NVARCHAR(260)
— Add any databases in that you want to exclude
DECLARE cdatabases CURSOR FOR
SELECT name
FROM   sysdatabases
WHERE  name != ‘tempdb’
AND name != ‘master’
AND name != ‘msdb’
–Set @restorepath to be the path of where your backups are located, in my example this is ‘D:\Backup\’
DECLARE @restorepath VARCHAR(500)
–@filename will need to be modified depending how the backup files are named
DECLARE @filename VARCHAR(500)
DECLARE @restorestring VARCHAR(1000)
–Not needed if running in a SQL job
DECLARE @exitcode INT
DECLARE @sqlerrorcode INT

OPEN cdatabases

FETCH next FROM cdatabases INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @restorepath = ‘D:\SQLVMDEV10\’ + @dbname + ‘\Full\’
— @filename will need to be modified depending how the backup files are named
SET @filename = @restorepath + ‘SQLVMDEV10_’ + @dbname + ‘_FULL_*.sqb’
SET @restorestring = ‘-SQL “RESTORE DATABASE [‘ + @dbname + ‘_Restored] FROM DISK = ”’ + @filename
+ ”’ SOURCE = ”’ + @dbname + ”’ LATEST_FULL WITH MAILTO = ”ed@sqlgator.com”, RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO ”G:\VirtualRestore”, MOVE LOGFILES TO ”G:\VirtualRestore”, REPLACE, ORPHAN_CHECK, CHECKDB = ”ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS”, DROPDB” -E’
–If you wish to apply additional backup, remember to change the WITH RECOVERY to WITH NORECOVERY,
EXEC master..Sqlbackup
@restorestring,
@exitcode output,
@sqlerrorcode output

–IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
—  BEGIN
—    RAISERROR (‘SQL Backup job failed with exitcode: %d  SQL error code: %d’, 16, 1, @exitcode, @sqlerrorcode)
—  END
FETCH next FROM cdatabases INTO @dbname
END

CLOSE cdatabases
DEALLOCATE cdatabases 

This will generate multiple entries in Red Gate Monitor, as mentioned in Part 4 of our journey.  In order to prevent these setup a maintenance window on your monitoring tool and run this script only during the maintenance window ensuring that no other jobs run during that time so that you do not miss any important alerts from those maintenance jobs.  Here is a link from Red Gate detailing how to set the maintenance windows in Monitor.  Enjoy!