I have a MSSQL 2008R2 DB in SIMPLE recovery mode that is periodically backed up with


This runs periodically, creating multiple backups sets in the file. I want to restore the latest backup that was made.

Doing this by hand I can run RESTORE HEADERONLY from Disk='Z:\DougHub.cbak' and then find the latest position to run RESTORE DATABASE MyDB from Disk='Z:\MyDB.cbak' with FILE = <some number> but I want to automate this step such that it always restores the most recent backup.

How do I create a TSQL command that will restore the most recent backup from the backup file?

  • Using the backupset table, like in Katherine's answer is what you want if you're restoring on the database where you created the backup. Otherwise, your best bet is to only ever have a single backup-set per file. ...Not that you asked. :)
    – Granger
    Feb 6 '14 at 6:26
  • I have scripts that do what Granger suggested, too, and am willing to share those as well. :) Feb 6 '14 at 14:25

Something like this may do the trick. I just quickly modified something I use for a slightly different purpose.

declare @dbname varchar(80),
        @lastfull datetime,
        @fullback varchar(1024),
        @position int,
        @SQL nvarchar(max)

set @dbname = 'YourDB'

select @lastfull = MAX(backup_finish_date) 
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D'
WHERE d.database_id NOT IN (2, 3) and d.name=@dbname

SELECT    @fullback = m.physical_device_name, @position = b.position
FROM         msdb.dbo.backupmediafamily AS m INNER JOIN
                      msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id 
                      and b.type='D' and b.database_name=@dbname
                      AND b.backup_finish_date=@lastfull

set @SQL =  'RESTORE DATABASE [' + @DBname + '] FROM  DISK = N''' + @fullback +  ''' WITH  FILE = ' + convert(nvarchar,@position) + ',  RECOVERY,  NOUNLOAD,  STATS = 10'

It pulls the last backup date for the database in question and fills in the path and position for you.

  • This will not work for backup set with multiple striped files.
    – user373182
    Aug 31 '16 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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