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);

• Kris Gruttemeyer: I do this exact process each day. I use sp_restorescriptgene (sqlservercentral.com/articles/Restore/111917) to retrieve the current restore script from production, then use a cursor to iterate through each file and restore it to my dev environment (we have a backdoor to the backups folder). I think your script that you have will only do the FULL backups and not the DIFFs/TLogs. Not saying your way is wrong at all, but you may not have to reinvent the wheel, here.
• Kin Shah: I agree with Kris - there is a cost of reinventing the wheel. You can use restoregene (as Kris mentioned) or the best way is using PowerShellas described here and here. Why not use something that is existing and modify it as per your needs ? Also, make sure that you sync up users and perform CHECKDB.
• Ali Razeghi - AWS: Have you considered Powershell? I made a few powershell functions and modified an autorestore script I found online to add a few features. Powershell picks the most recent file, checks to see if it was written within an acceptable time frame, then passes all that info to SQL Server via invoke-sqlcmd.
• Geoff Dawdy: I've modified the question. If there is a better method for performing the task of automating restores, I'm open to suggestions.

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;

• Michael Green: order .. desc to get the newest one.