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.database_name,
s.backup_start_date,
s.first_lsn, s.last_lsn,
s.database_backup_lsn,
s.checkpoint_lsn,
 s.[type],-- D--FULL,I--Diff,L--LOG
 m.physical_device_name, 
s.server_name,s.recovery_model
,s.backup_finish_date
,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

 
 

 
Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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