$Query = "declare @last_full_backup table (database_name sysname, last_lsn numeric(25,0))   

insert into @last_full_backup (database_name, last_lsn)
select database_name, MAX(last_lsn) 
from msdb.dbo.backupset b 
INNER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
where type = 'D'  and is_copy_only = 0 
group by database_name
order by database_name


SELECT $groupid, b.type, b.database_name, b.backup_finish_date,  physical_device_name,
'RESTORE DATABASE [' + b.database_name + '] FROM ' +
(
STUFF((
select 
', DISK = N' + '''' +  '$RestorePathSY6' + CASE b.type When 'D' Then '\FULL\' When 'I' Then '\DIFF\' When 'L' Then '\Log\' END +substring(physical_device_name, len(physical_device_name) - CHARINDEX('\',reverse(physical_device_name))+2, len(physical_device_name) ) + '''' + ' '  
from 
msdb.dbo.backupmediafamily mf
where  b.media_set_id = mf.media_set_id
FOR XML PATH ('')), 1, 2, '')) +
' WITH  FILE = 1,  ' +
STUFF((
select '  MOVE N'+ '''' + m.name + '''' + ' TO N' + '''' + '$DataFolder' + 
substring(physical_name, len(physical_name) - CHARINDEX('\',reverse(physical_name))+2, len(physical_name) ) + '''' + ','
from
 master.sys.master_files m,
 sys.databases d
 where m.database_id = d.database_id
 and d.name = b.database_name
 and m.[type] = 0
FOR XML PATH ('')), 1, 1, '') + 
STUFF((
select '  MOVE N'+ '''' + m.name + '''' + ' TO N' + '''' + '$LogFolder' + 
substring(physical_name, len(physical_name) - CHARINDEX('\',reverse(physical_name))+2, len(physical_name) ) + '''' + ','
from
 master.sys.master_files m,
 sys.databases d
 where m.database_id = d.database_id
 and d.name = b.database_name
 and m.[type] = 1
FOR XML PATH ('')), 1, 1, '') +
'NORECOVERY,  NOUNLOAD,  STATS = 5' as RestoreCommandSY6,
'RESTORE DATABASE [' + b.database_name + '] FROM ' +
(
STUFF((
select 
', DISK = N' + '''' +  '$RestorePathSY7' + CASE b.type When 'D' Then '\FULL\' When 'I' Then '\DIFF\' When 'L' Then '\Log\' END +substring(physical_device_name, len(physical_device_name) - CHARINDEX('\',reverse(physical_device_name))+2, len(physical_device_name) ) + '''' + ' '  
from 
msdb.dbo.backupmediafamily mf
where  b.media_set_id = mf.media_set_id
FOR XML PATH ('')), 1, 2, '')) +
' WITH  FILE = 1,  ' +
STUFF((
select '  MOVE N'+ '''' + m.name + '''' + ' TO N' + '''' + '$DataFolder' + 
substring(physical_name, len(physical_name) - CHARINDEX('\',reverse(physical_name))+2, len(physical_name) ) + '''' + ','
from
 master.sys.master_files m,
 sys.databases d
 where m.database_id = d.database_id
 and d.name = b.database_name
 and m.[type] = 0
FOR XML PATH ('')), 1, 1, '') + 
STUFF((
select '  MOVE N'+ '''' + m.name + '''' + ' TO N' + '''' + '$LogFolder' + 
substring(physical_name, len(physical_name) - CHARINDEX('\',reverse(physical_name))+2, len(physical_name) ) + '''' + ','
from
 master.sys.master_files m,
 sys.databases d
 where m.database_id = d.database_id
 and d.name = b.database_name
 and m.[type] = 1
FOR XML PATH ('')), 1, 1, '') +
'NORECOVERY,  NOUNLOAD,  STATS = 5' as RestoreCommandSY7
from
msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
join @last_full_backup lfb
on b.last_lsn >= lfb.last_lsn and b.database_name = lfb.database_name
--where b.type = 'D' or b.type = 'L'
order by b.database_name, b.backup_finish_date desc
"