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
insert into #headers exec('restore headeronly from disk=''c:\DEV\UnitTest.bak''');
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'
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
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'