Since all the details on backups are maintained in the msdb database, you should just extract the backup file name from the source server.
You could create a linked server from your Dev Server to access the Production Server's msdb database. Or you can use OPENQUERY to query the same data. (OPENQUERY may be faster since the query is actually being run on the Production Server.)
For example:
SELECT * from OPENQUERY([LinkToPRD],
'EXEC database.dbo.ExecDailyRestore');
This shows running a stored procedure with no parameters, which might suit your daily restore plan.
If you search for "auto generate sql server database restore scripts" you will find many scripts. An example from Paul Brewer is sp_RestoreGene
that you might use as is or as a basis to create your own ExecDailyRestore
stored procedure.
https://paulbrewer.wordpress.com/sp_restoregene/
Here are the parameters supported by sp_restoregene:
@Database SYSNAME = NULL,
@TargetDatabase SYSNAME = NULL,
@WithMoveDataFiles VARCHAR(2000) = NULL,
@WithMoveLogFile VARCHAR(2000) = NULL,
@FromFileFullUNC VARCHAR(2000) = NULL,
@FromFileDiffUNC VARCHAR(2000) = NULL,
@FromFileLogUNC VARCHAR(2000) = NULL,
@StopAt DATETIME = NULL,
@StandbyMode BIT = 0,
@IncludeSystemDBs BIT = 0,
@WithRecovery BIT = 0,
@WithCHECKDB BIT = 0,
@WithReplace BIT = 0,
@UseDefaultDatabaseBackupPath BIT = 0,
@Log_Reference VARCHAR (250) = NULL,
@LogShippingVariableDeclare BIT = 1,
@LogShippingStartTime DATETIME = NULL,
@LogShippingLastLSN VARCHAR(25) = NULL
And here is a sample script:
RESTORE DATABASE db_workspace
FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE,
FILE = 1,CHECKSUM,NORECOVERY, STATS=10
, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf'
, MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'