List of all backups taken after the latest full backup

Backup, General 1 Minute

To find the list of all the backups taken on a database after the latest Full Backup(including), you can use the below query:

declare @DatabaseName varchar(200)
set @DatabaseName = 'DatabaseName';

select backup_set_id,database_name,backup_size,type as BackupType,A.name as BackupName,Backup_Start_Date,Backup_Finish_date,Physical_device_name from msdb.dbo.backupset A INNER JOIN msdb.dbo.backupmediafamily B on A.media_set_id=B.media_set_id where database_name = @DatabaseName and backup_set_id>=(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type='D')

This information can be useful when you are trying to figure out which/how many backups you should restore, if you want to bring the database to point in time(remember, you should also take Tail log Backup,if possible) and Also, this query works if you have Full, Differential, Log backup implementation

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published