Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a stored proc which takes the backup from a database to the specified location. Sometimes the backup process runs more than once. It causes having more than one backup set on the same file name. When we write T-SQL to restore the target db using this backup file, how can we tell SQL to restore the latest db backup set on the target database (using the T-SQL command)?

When we restore the backup using MS SQL management studio, we can simply select the back up set we want to restore. But I need to run the restore using T-SQL and wondering how can determine SQL uses the latest db backup set to restore. For example from the image below, I want to restore the last backup set (highlighted in yellow):

I checked this website, but couldn't find the answer. I appreciate it if you could help.

You now have a temporary table(#headers) which contains details of all the backups contained within that device file.

To find the most recent full database backup we filter for only full db backups (backupType=1) and then look for the most recent backup in the device, which will be the one with the largest LSN. This gives us the position value, which is used in the RESTORE command to specify to SQL Server which backup we want to restore:

As you say you will still have access to the original server, we can then query the tables in msdb that SQL Server uses to store this information.

The following queries are assuming you are running them in a SQL session on the original server. If you're not allowed to do that, then you'll need to look into using a linked server and 4 part naming.

There are 2 ways you can specify the set of backups you want to look at. One is via the path to the backup file, the other is by referencing the name on the backup device. I've included both in this query, with some notes about which lines to comment out depend on how you're doing it.

This will produce a RESTORE statement which you can run in a seperate SSMS session. I've also included an example at the bottom of how you could automate this, though you'd want to do some testing beforehand to make sure it's doing what you want.

declare @filepos int, @dbname varchar(50), @devname varchar(50)
/*
dbname is the name of the database you want to restore. Need this, as it's possible for backups of more than one database to be present in the same backup file or device
devname is either the name of the backup device, or the path to the .bak file
*/
select @dbname='Unittest', @devname='c:\DEV\unittest.bak'
select @filepos=max(c.position)
from
msdb.dbo.backupmediafamily a
inner join msdb.dbo.backupmediaset b on a.media_set_id=b.media_set_id
inner join msdb.dbo.backupset c on b.media_set_id=c.media_set_id
where
a.physical_device_name=@devname --use this line if you know the path to the backup
--a.logical_device_name=@devname --this line if you only know a device name.
and c.type='D' and c.database_name=@dbname
--This will build the restore statement if you know the device name
select 'restore database ['+@dbname+'] from '+@devname+' with file='+cast(@filepos as varchar(4))+', recovery, replace'
--This will build the restore statement if you know the backup file path
select 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
/*
This section only if feeling brave and confident, and you've checked the output previously, as it will execute whatever the generated restore command is, so this could cause a major problem if not checked first, or even overwrite production if run on the wrong box.
*/
declare @restore_cmd varchar(250)
select @restore = 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
exec(@restore)