When we need to restore a database, the very first thing we need to find is list of backups that needs to be restored.Of course, you might need to take tail log backup and restore at the very end based on the situation you are in. The query below will list all the EXISTING backups of a particular database to be restored
I have a similar post here,however, the difference between these two scripts is that the below query will list only the backups that needs to be restored where as the previous one will list all the backups after the latest full backup.
The query first checks for latest full backup and latest differential backup, if any, and all the log backups after the latest differential or full backup.I am eliminating the Copy_Only Backups, so the restore sequence chain is not interrupted.
Note:The query will only work on SQL 2005 and above versions.
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' and is_copy_only=0) and is_copy_only=0
union
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
A.backup_set_id =
(Select max(backup_set_id) from msdb.dbo.backupset where database_name= @DatabaseName and type in ('I','D') and is_copy_only=0) and type = 'I'
union
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 in ('I','D') and is_copy_only=0) and type = 'L' and is_copy_only=0