Restore most current backup.

bbingay

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

"FULL_(local)_MYBACKUP_20080701_203440.sqb".

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.

Thanks

Bryan

Matthew Flatt

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 @SQL VARCHAR(1000)
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
b.physical_device_name
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 = '''
+ @SQL + ''' WITH RECOVERY, REPLACE" -E'

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

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

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.