MSSQL: Restore a DB to the latest file in a backup set

Question

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

BACKUP DATABASE MyDB to DISK = 'Z:\MyDB.cbak'  WITH COMPRESSION

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 06 '14 at 06:26
I have scripts that do what Granger suggested, too, and am willing to share those as well. :) — Katherine Villyard, Feb 06 '14 at 14:25

Katherine Villyard · Accepted Answer · 2014-02-06T14:23:31.427

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'
EXEC SP_EXECUTESQL @SQL

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. — , Aug 31 '16 at 03:40

MSSQL: Restore a DB to the latest file in a backup set

1 Answers1