"RESTORE..... WITH MOVE" all databases on a server

create procedure sp_RestoreDBs

/* make sure QA is in "Results in Text" mode or "Results to File" mode when you execute */
@DBString nvarchar(4000), /* comma-delimited string of databases to restore; 'all' = all user databases */@BackupFilePath nvarchar(4000) = '\\bhdbdev\e$\',
@BackupFileSuffix sysname = '.bak',
@LogicalDataFileSuffix sysname = '_Data',
@LogicalLogFileSuffix sysname = '_Log',
@PhysicalDataFileSuffix sysname = '_Data.mdf',
@PhysicalLogFileSuffix sysname = '_Log.ldf'

/* note: you can change these default parameter values to match your environment in order to make your work easier */
as

set nocount on

if @DBString = 'all' or @DBString is null
begin 
/* grab comma-delimited list of all user databases */
select @DBString = 
isnull(nullif(@DBString, 'all') + ',', '') + name
from sysdatabases
where name not in ('master', 'model', 'msdb', 'tempdb')

end

/* put individual database names in single quotes */
set @DBString = replace(@DBString, ',', ''',''')

/* add single quotes to beginning and end of string */
set @DBString = '''' + @DBString + ''''

/* put DBs in single user mode; 
comment out 'with rollback immediate' if you are using SQL 7
as this is not supported */
exec('select ''alter database '' + name + ''
set single_user
with rollback immediate''
from sysdatabases
where name in (' + @DBString + ')')

exec('select ''restore database '' + name + ''
from disk = ''''' + @BackupFilePath + ''' + name + ''' + @BackupFileSuffix + '''''
with move '''''' + name + ''' + @LogicalDataFileSuffix + ''''''' + '' to ''''' +
''' + filename + '''''',
move '''''' + name + ''' + @LogicalLogFileSuffix + ''''''' + '' to '''''' +
replace(filename ,''' + @PhysicalDataFileSuffix + ''', ''' + @PhysicalLogFileSuffix + ''')' + ' + ''''''''
from sysdatabases
where name in (' + @DBString + ')')

/* put DBs back in multi user mode */
exec('select ''alter database '' + name + ''
set multi_user''
from sysdatabases
where name in (' + @DBString + ')')