Automatically restore SQL Server database from file from another server

We have a daily task to overwrite a number of development databases using backups of the associated production databases. The backups are produced by maintenance plans on the production server then transferred to the dev server by FTP. Each day we run a SQL statement similar to this to overwrite each database:

RESTORE DATABASE [Database1] 
FROM DISK = N'D:\path\to\Database1_backup_2015_02_05_190004_7401803.bak'
WITH FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Each time we run this we have to replace the file name with the correct most recent file. I would like to automate this somehow to minimise the chance of operator error. The problem is that we can't control the name of the .bak file (although the format is consistent - database name, date, time and whatever that seven digit number is), and the folder will usually contain several days worth of backups.

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'

StackExchange Database Administrators Q#91223, answer score: 10