This will 
Drop the local copy of the database.
Look at the directory @remotepath.
Find the latest full backup for @dbname (expects backup file name to be _full_yyyymmdd_hhmmss.bak - see code for allowable variations)
Delete all files from @localpath.
Copy the backup to @localpath.
Get the logical file names from the backup.
Build a restore command moving the files to the directory @localpath.
Execute the restore.
Email the result to @recipients.



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_TestRestore]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_TestRestore]
GO

create proc s_TestRestore
@dbname		varchar(128) ,
@remotepath 	varchar(200) ,
@localpath 	varchar(200) ,
@recipients	varchar(128)
as

/*
exec s_TestRestore
	@dbname = 'testdb' ,
	@remotepath = '\\dbsever\backup\full\' ,
	@localpath = 'c:\TestRestore\'
*/	

declare @cmd 		varchar(2000) ,
	@filename	varchar(128) ,
	@s		varchar(128) ,
	@i		int ,
	@d		datetime ,
	@sql		nvarchar(2000) ,
	@StartDate	datetime
	
	select	@StartDate = getdate()
	
	if exists (select * from master..sysdatabases where name = @dbname)
	begin
		select @cmd = 'drop database ' + @dbname
		exec (@cmd)
	end
	
	select @cmd = 'dir /B ' + @remotepath + @dbname + '*.*'
	create table #a (s varchar(2000))
	insert #a exec master..xp_cmdshell @cmd
	
	delete	#a 
	where s is null
	or s not like '%full%'
	
	select 	@filename = max(s) from #a
	
	select @cmd = 'del /Q ' + @localpath + '*.*'
	exec master..xp_cmdshell @cmd
	
	select @cmd = 'xcopy ' + @remotepath + @filename  + ' ' + @localpath
	exec master..xp_cmdshell @cmd
	
	select @cmd = 'restore filelistonly  from disk = ''' + @localpath + @filename + ''''
	
	create table #files (lname varchar(128), pname VARCHAR(128), type varchar(10), fgroup varchar(128), size varchar(50), maxsize varchar(50))
	insert #files exec (@cmd)
	
	-- now buld the restore
	select 	@cmd = null ,
		@s = ''
	
	while @s < (select max(lname) from #files)
	begin
		select @s = min(lname) from #files where lname > @s
		
		select @cmd = coalesce(@cmd + ',move ', '') + '''' + @s + ''' to ''' + @localpath + @s + ''''
	end
	select @cmd = 'restore database ' + @dbname + ' from disk = ''' + @localpath + @filename + ''' with move ' + @cmd 		-- + ', standby = ''' + @localpath + 'standby.fil'''
	select (@cmd)
	exec (@cmd)
	
	drop table #files
	drop table #a
	
	select @sql = 'select @i = count(*) from ' + @dbname + '..sysobjects select @d = crdate from master..sysdatabases where name = ''' + @dbname + ''''
	exec sp_executesql @sql, N'@i int out, @d datetime out', @i out, @d out
	
	if @d > @StartDate and @i > 20
	begin
		select @cmd = 'restore ' + @filename + ' completed successfully - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
		exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
	end
	else
	begin
		select @cmd = '********************* restore ' + @filename + ' failed - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
		exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
	end

go