Restore the latest backup on the database

Question

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.

Thanks.

Answer 1

If you don't have access to the original server and all you have is the backup file, then you'll need to restore the headers from the backup file first to see what's inside it.

You can do this by:

create table #headers 
(
BackupName              nvarchar(128),
BackupDescription       nvarchar(255),
BackupType              smallint,
ExpirationDate          datetime,
Compressed              bit,
Position                smallint,
DeviceType              tinyint,
UserName                nvarchar(128),
ServerName              nvarchar(128),
DatabaseName            nvarchar(128),
DatabaseVersion         int,
DatabaseCreationDate    datetime,
BackupSize              numeric(20,0),
FirstLSN                numeric(25,0),
LastLSN                 numeric(25,0),
CheckpointLSN           numeric(25,0),
DatabaseBackupLSN       numeric(25,0),
BackupStartDate         datetime,
BackupFinishDate        datetime,
SortOrder               smallint,
[CodePage]              smallint,
UnicodeLocaleId         int,
UnicodeComparisonStyle  int,
CompatibilityLevel      tinyint,
SoftwareVendorId        int,
SoftwareVersionMajor    int,
SoftwareVersionMinor    int,
SoftwareVersionBuild    int,
MachineName             nvarchar(128),
Flags                   int,
BindingID               uniqueidentifier,
RecoveryForkID          uniqueidentifier,
Collation               nvarchar(128),
FamilyGUID              uniqueidentifier,
HasBulkLoggedData       bit,
IsSnapshot              bit,
IsReadOnly              bit,
IsSingleUser            bit,
HasBackupChecksums      bit,
IsDamaged               bit,
BeginsLogChain          bit,
HasIncompleteMetaData   bit,
IsForceOffline          bit,
IsCopyOnly              bit,
FirstRecoveryForkID     uniqueidentifier,
ForkPointLSN            numeric(25,0),
RecoveryModel           nvarchar(60),
DifferentialBaseLSN     numeric(25,0),
DifferentialBaseGUID    uniqueidentifier,
BackupTypeDescription   nvarchar(60),
BackupSetGUID           uniqueidentifier,
CompressedBackupSize    bigint,
Containment             tinyint
);
go

insert into #headers exec('restore headeronly from disk=''c:\DEV\UnitTest.bak''');
go 

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:

declare @filepos int;
select top 1 @filepos=Position from #headers where BackupType=1 order by lastlsn desc;
restore database unittest from Disk=N'c:\DEV\UnitTest.bak' with file=@filepos, recovery, replace;

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)

Answer 2

This should give you a start:

SELECT 
  s.name, 
  s.backup_start_date, 
  s.backup_finish_date, 
  mf.physical_device_name,
  s.first_lsn, s.last_lsn, s.database_backup_lsn
FROM 
  msdb.dbo.backupset AS s
INNER JOIN 
  msdb.dbo.backupmediaset AS ms
  ON s.media_set_id = ms.media_set_id
INNER JOIN 
  msdb.dbo.backupmediafamily AS mf
  ON ms.media_set_id = mf.media_set_id
WHERE 
  s.database_name = N'UnitTest' 
ORDER BY 
  s.backup_finish_date DESC;