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

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

"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

Comments

  • 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 @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.
    Matthew Flatt
    Redgate Foundry
Sign In or Register to comment.