Get Most Recent Backup Chain in Full, Differential and Log Backups

 use [myDB];

declare @dt datetime=getdate()
declare @dbname sysname= (select DB_NAME())
declare @server_name sysname ='DEVSQL'
;WIth mycte as (SELECT
s.first_lsn, s.last_lsn,
 s.[type],-- D--FULL,I--Diff,L--LOG
,row_number() Over(Partition by s.[type] Order by s.backup_finish_date DESC ) rn
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @dbname
AND server_name=@server_name
AND s.backup_finish_date >=(select top 1 s.backup_finish_date from msdb.dbo.backupset s 
Where s.[type]='D' and s.server_name= @server_name AND s.database_name = @dbname 
AND s.backup_finish_date<= @dt
Order by s.backup_finish_date DESC)
AND s.backup_finish_date<= @dt
Select * from mycte
WHERE (rn=1 and [type]<>'L') Or (last_lsn >= (select last_lsn+1 from mycte WHERE rn=1 and [type]='I') )
ORDER BY backup_finish_date desc,backup_start_date desc



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s