Automate SQL Server restore from Prod to Dev

Question

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the prod backup location to restore from. However, anytime I want to run this restore job I need to go in and manually change the file so that it's pointing to the latest one. Is there a method, preferably using T-SQL, which will grab the latest .bak file to use in the restore job? If there is a better method for doing this then I'm open to suggestions. I definitely don't want to reinvent the wheel.

Below is the current code I'm using to perform the restore. I have it narrowed down to where I only need to change the database name and backup file location.

DECLARE @DB nvarchar(max)   /** Database to be restored **/
, @SQL nvarchar(max)        /** Dynamic SQL **/
, @disk nvarchar(max)       /** Backup location **/
, @l_mdf nvarchar(max)      /** Logical primary data file **/
, @p_mdf nvarchar(max)      /** Physical primary data file **/
, @l_ldf nvarchar(max)      /** Logical Log data file **/
, @p_ldf nvarchar(max)      /** Physical Log data file **/
, @dp nvarchar(200)         /** Datafile path **/
, @lp nvarchar(200)         /** Logfile path **/

/*** These are the only variables to manually set ***/
SET @db = 'PRODBackup'
SET @disk = '\\PROD_UNC_PATH\MSSQL10.SQL2008\MSSQL\Backup\PRODbackup_2015_04_05_220001_1759944.bak'

/*** This builds a file list table to be populated by executing filelistonly for the backup file ***/
declare @fileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32) -- remove this column if using SQL 2005
)
/*** Insert into aforementioned table ***/
insert into @fileListTable exec('restore filelistonly from disk =''' +@disk+'''')

/*** Set variables based on this table using filters and string manipulation ***/
    /** datafile **/
select @l_mdf = logicalName from @fileListTable where Type='D'
select @p_mdf = PhysicalName from @fileListTable where Type='D'
select @p_mdf = RIGHT(@p_mdf, CHARINDEX('\',REVERSE(@p_mdf))-1)

    /** logfile **/
select @l_ldf = logicalName from @fileListTable where Type='L'
select @p_ldf = PhysicalName from @fileListTable where Type='L'
select @p_ldf = RIGHT(@p_ldf, CHARINDEX('\',REVERSE(@p_ldf))-1)
/*** Set default data and log paths ***/
--select @dp = CAST(serverproperty('instancedefaultdatapath') AS nvarchar(200))
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dp output, 'no_output' 
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @lp output, 'no_output' 

/*** Execute dynamic SQL ***/
SET @SQL = ('USE [master]
            RESTORE DATABASE ['+@DB+'] 
                FROM  DISK = N'''+@disk+''' 
                    WITH  FILE = 1
                ,  MOVE N'''+@l_mdf+''' TO N'''+@dp + @p_mdf+'''
                ,  MOVE N'''+@l_ldf+''' TO N'''+@lp + @p_ldf+'''
                ,  NOUNLOAD,  REPLACE,  STATS = 5;')
EXEC (@SQL);

Answer 1

Assuming your full backups are always done WITH INIT:

  SELECT TOP (1) @disk = f.physical_device_name
    FROM msdb.dbo.backupmediafamily AS f
    INNER JOIN msdb.dbo.backupset AS s
    ON f.media_set_id = s.media_set_id
    WHERE s.database_name = @db
      AND s.[type] = N'D'
    ORDER BY backup_finish_date;