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.
Best Answer
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:
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 theRESTORE
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.