Build Restore Scripts for All User Databases

use master
go
Declare   @BAKDir varchar(255)
 

Set nocount on
Select @BAKDir = 'E:\sql8\backup'

Select 'Use ' + name + char(10) + 'go' + char(10) + 'Set nocount on' + Char(10) + 
'Select ''RESTORE DATABASE '' + Rtrim(db_name()) + '' FROM Disk=''''' + @BAKDir + '\D_'' + rtrim(name) + ''.bak'''' WITH Replace, NORECOVERY '' from master.dbo.sysdatabases where name = Rtrim(db_name())'
+ char(10) + 'Set nocount on' + Char(10) + 'select ''     , MOVE '''''' + rtrim(name) + '''''' TO '''''' + rtrim(filename) + '''''''' from sysfiles order by fileid' 
+ char(10) + 'Set nocount on' + Char(10) + 
'Select ''go'''
+ char(10) + 'go'
  from sysdatabases 
where NOT(name IN ('master','tempdb','model','msdb'))
Order by name