Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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. :) – Katherine Villyard Feb 6 '14 at 14:25
up vote 4 down vote accepted

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 = b.database_name AND b.type = 'D'
WHERE d.database_id NOT IN (2, 3) and

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.

share|improve this answer
Perfect, thanks! – DrStalker Feb 6 '14 at 22:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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