What are the challenges you face when working across database platforms? Take the survey

Restore most current backup.

bbingaybbingay Posts: 8
edited July 11, 2008 6:52AM in SQL Backup Previous Versions
Hi, I have a server that keeps 3 days of full backups on disk. Occasionally I need to restore the most current full backup in test and dev. Backup files are named like this


Every backup file has the date and time appended to it.

I would like to be able to run a T-SQL command that will find the most current full backup in the directory. Currently I need to change the file name every time I need to restore. My goal is to setup a SQL agent job that will restore the backup in test and dev without any changes to the restore script. Any ideas or ways to do this are appreciated.




  • Options
    Hi Bryan,

    Thank you for your post into the forum.

    I have created the following script which will restore the latest full backup of a database. The script will not find the backup file if it has been moved from the original primary location, it will also overwrite the existing database.

    The script is as follows :

    DECLARE @restorestring VARCHAR(1000)
    DECLARE @exitcode INT
    DECLARE @sqlerrorcode INT

    SELECT @SQL = b.physical_device_name
    FROM msdb..backupmediafamily b
    WHERE b.physical_device_name = ( SELECT TOP 1
    FROM msdb..[backupset] a
    INNER JOIN msdb..backupmediafamily b ON a.[media_set_id] = b.[media_set_id]
    WHERE a.[database_name] = 'WidgetDevEmpty'
    AND a.TYPE = 'D'
    ORDER BY a.backup_start_date DESC

    SET @restorestring = '-SQL "RESTORE DATABASE [WidgetDevEmpty] FROM DISK = '''

    EXEC MASTER..sqlbackup @restorestring, @exitcode OUTPUT, @sqlerrorcode OUTPUT

    IF ( @exitcode <> 0 )
    OR ( @sqlerrorcode <> 0 )
    RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d',
    16, 1, @exitcode, @sqlerrorcode )

    You will need to swap the WidgetDevEmpty database with your database name, feel free to edit and adapt the script to your needs.

    Please let me know if this is useful.
    Matthew Flatt
    Redgate Foundry
Sign In or Register to comment.