This will 
Drop the local copy of the database.
Get the latest backup from @localBackupPath.
Get the logical file names from the backup.
Build a restore command moving the files to the directories @localDBPath, @localLogPath.
Execute the restore.
Email the result to @recipients.



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

create proc s_RestoreLatestBackup
@dbname			varchar(128) ,
@localBackupPath 	varchar(200) ,
@localDBPath 		varchar(200) ,
@localLogPath		varchar(200) ,
@recipients		varchar(128)
as

/*
exec s_TestRestore
	@dbname = 'testdb' ,
	@localBackupPath = 'c:\TestRestore\' ,
	@localDBPath = 'c:\TestRestore\' ,
	@localLogPath = 'c:\TestRestore\' ,
	@recipients = 'myemailaddress'
*/	

declare @cmd 		varchar(2000) ,
	@filename	varchar(128) ,
	@s		varchar(128) ,
	@i		int ,
	@d		datetime ,
	@sql		nvarchar(2000) ,
	@StartDate	datetime
	
	select	@StartDate = getdate()
	
	-- drop database
	if exists (select * from master..sysdatabases where name = @dbname)
	begin
		select @cmd = 'drop database ' + @dbname
		exec (@cmd)
	end
	
	-- get latest backup filename
	select @cmd = 'dir /B ' + @localBackupPath + @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
	
	-- Get files in backup
	select @cmd = 'restore filelistonly  from disk = ''' + @localBackupPath + @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)
	
	-- buld the restore command
	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 ''' + case when #files.type = 'D' then @localDBPath else @localLogPath end  + @s + ''''
		from	#files 
		where 	lname = @s
	end
	select @cmd = 'restore database ' + @dbname + ' from disk = ''' + @localBackupPath + @filename + ''' with move ' + @cmd 		-- + ', standby = ''' + @localBackupPath + 'standby.fil'''

	-- restore the database
	select (@cmd)
	
	exec (@cmd)
	
	drop table #files
	drop table #a
	
	-- Check that the restore was successful
	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
		if @recipients is not null
		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
	end
	else
	begin
		select @cmd = '********************* restore ' + @filename + ' failed - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
		if @recipients is not null
		begin
			exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
		end
		raiserror(@cmd, 16, -1)	
	end

go