Restore multiple databases in SQL Backup

This article will describe how to restore every database for a set instance rather than restoring each database individually through the SQL Backup GUI.

The SQL Backup GUI doesn't offer the ability to restore multiple databases and thus a script must be used to achieve this. By editing the below script with the correct variables you can restore all of the backup files in a folder provided there is only one FULL backup file per database.

USE master
declare @dbname nvarchar(260)
declare cDatabases CURSOR FOR SELECT name from sysdatabases where name!='tempdb' AND name!='master' AND name!='msdb'
declare @datestamp varchar(30)
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)
declare @exitcode int
declare @sqlerrorcode int

--Set @restorepath to be the path of where your backups are located, in my example this is 'D:\Backup\'
set @restorepath = 'D:\Backup\'

OPEN cDatabases
FETCH NEXT FROM cDatabases INTO @dbname

WHILE @@FETCH_STATUS=0
BEGIN
set @filename = @restorepath + 'FULL_SQL2005_' + @dbname + '_*.sqb' -- @filename will need to be modified depending how the backup files are named

set @restorestring = '-SQL "RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @filename + ''' WITH RECOVERY" -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

NOTE: this is a workaround that may need to be tweaked to fit specific scenarios.