This will 
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)
Optionally delete backup files from @localpath.
Copy the backup to @localpath.



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

create proc s_CopyLatestBackup
@dbname			varchar(128) ,
@remotepath 		varchar(200) ,
@localpath 		varchar(200) ,
@DeleteLocalBackups	varchar(1)		-- Y/N
as

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

declare @cmd 		varchar(2000) ,
	@filename	varchar(128) ,
	@s		varchar(128) ,
	@i		int ,
	@d		datetime ,
	@sql		nvarchar(2000) ,
	@StartDate	datetime
	
	select	@StartDate = getdate()
	
	create table #a (s varchar(2000))
	
	-- get latest backup from remote path
	select @cmd = 'dir /B ' + @remotepath + @dbname + '*.*'
	insert #a exec master..xp_cmdshell @cmd
	
	delete	#a 
	where s is null
	or s not like '%full%'
	
	select 	@filename = max(s) from #a
	
	-- check local file
	delete	#a 
	
	select @cmd = 'dir /B ' + @localpath + @dbname + '*.*'
	insert #a exec master..xp_cmdshell @cmd
	
	delete	#a 
	where s is null
	or s not like '%' + @filename + '%'
	
	if exists (select * from #a)
	begin
		-- we already have this file
		select @cmd = 'file ' + @filename + ' already transferred'
		raiserror(@cmd, 16, -1)
		return
	end
	
	drop table #a
	
	-- delete local backups
	if @DeleteLocalBackups = 'Y'
	begin
		select @cmd = 'del /Q ' + @localpath + '*' + @dbname + '*full*.*'
		exec master..xp_cmdshell @cmd
	end
	
	select @cmd = 'xcopy ' + @remotepath + @filename  + ' ' + @localpath
	exec master..xp_cmdshell @cmd
	
go