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;')
  • 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. Apr 6, 2015 at 15:59
  • 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.
    – Kin Shah
    Apr 6, 2015 at 16:05
  • 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. Apr 6, 2015 at 17:53
  • I've modified the question. If there is a better method for performing the task of automating restores, I'm open to suggestions. Apr 6, 2015 at 18:32

1 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;
  • order .. desc to get the newest one. Jul 30, 2015 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.