Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 3 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 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.

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

Your Answer

 
discard

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.