use master 
set nocount on;
declare @replicas table (rname varchar(128))
declare @backups table (srv varchar(128),database_name nvarchar(256),[recovery] varchar(30),[status] varchar(30),type char(1),last_backup datetime, first_backup datetime)
declare @name varchar(128)
declare @cmd varchar(8000)
declare @flag tinyint = 0
declare @remote_failed tinyint = 0

if object_id('sys.availability_replicas') is not null /*compatibility with SQL2008x*/
	begin
		set @cmd='select distinct replica_server_name from sys.availability_replicas where replica_server_name <>@@SERVERNAME'
		insert into @replicas(rname)
		exec (@cmd)
	end
 
if @@ROWCOUNT>0 and (select value_in_use from sys.configurations where name='Ad Hoc Distributed Queries' ) = 0
	begin
		set @cmd='exec sp_configure ''Ad Hoc Distributed Queries'',1; reconfigure with override;'

		select @cmd=isnull('exec sp_configure ''show advanced options'',1; reconfigure with override; ','')+@cmd
		from sys.configurations
		where name='show advanced options'
		and value_in_use=0

		exec(@cmd)

		set @flag=1
	end

while exists (select top 1 1 from @replicas)
	begin
		select top 1 @name=rname
		from @replicas
		
		set @cmd='select * FROM OPENROWSET(''SQLNCLI'', ''Server='+@name+';Trusted_Connection=yes;'',  
					''select @@servername [srv],database_name,convert(varchar(30),DATABASEPROPERTYEX(database_name,''''Recovery'''')) [recovery],convert(varchar(30),DATABASEPROPERTYEX(database_name,''''Status'''')) [status],[type],max(backup_start_date) [last], min(backup_start_date) [first] from msdb..backupset where database_name in (select db_name(database_id) from sys.dm_hadr_database_replica_states where is_local=1) group by database_name,type'' ) AS a;'  


BEGIN TRY  
		insert into @backups ([srv],[database_name],[recovery],[status],[type],[last_backup],[first_backup])
		exec(@cmd)
END TRY  

BEGIN CATCH  
		set @remote_failed=1
END CATCH  

		delete @replicas 
		where rname=@name
	end

if @flag=1
	begin
		set @cmd='exec sp_configure ''Ad Hoc Distributed Queries'',0; reconfigure with override;'
		exec (@cmd)
	end;


with  
------------
data as 
(
	select [srv],[database_name],[recovery],[status],[type],[last_backup],[first_backup]
	from @backups
	union all
	select 
		@@servername [srv]
		,d.name
		,d.recovery_model_desc collate SQL_Latin1_General_CP1_CI_AI
		,d.state_desc collate SQL_Latin1_General_CP1_CI_AI
		,isnull(type,'A')
		,isnull(max(backup_start_date),0) [last]
		,isnull(min(b.backup_start_date),0) [first]
	from sys.databases d
	left join msdb..backupset b on d.name=b.database_name collate SQL_Latin1_General_CP1_CI_AI
	where d.database_id<>2
		and b.server_name=@@SERVERNAME  /*check local backups; exclude imported backup info (!) from restore command*/
		
	group by d.name,b.type,d.recovery_model_desc,d.state_desc
)--------------------------
,data2 as 
(
	select 
		d.name [database_name]
		,d.state_desc
		,d.recovery_model_desc
		,isnull(b.type,'A') [BackupType]   /*if there were no backups at all made on the server OR backupset table is empty, then 'A' */
		,isnull(max(b.last_backup),0) [LastBackup]
		,isnull(datediff(dd,max(b.last_backup),getdate()),-1) [Days]
	from data b
	right join sys.databases d on b.database_name=d.name and not (d.recovery_model_desc = N'SIMPLE' and b.type='L') /*b.type='L'  -- log backup*/
	where 
		d.database_id<>2
		and d.source_database_id is null
		-- and not (d.recovery_model_desc = N'SIMPLE' and b.type='L') 
	group by 	
		d.name
		,d.state_desc
		,d.recovery_model_desc
		,b.[type]
	having isnull(max(b.last_backup),0)<=dateadd(dd,case 
														when b.[type]='L' then -1 
														when b.[type]='D' then -7 
														else -3 /*for all other types of backups*/
													end
												,convert(date,getdate())
												)
) 
 
select 
	case 
		when [BackupType]='D' then 'FULL' 
		when [BackupType]='L' then 'LOG'  
		when [BackupType]='A' then '*** ALL ***' 
		when [BackupType]='I' then 'DIFF'
		when [BackupType]='F' then 'File/Filegroup'
		when [BackupType]='G' then 'DIFF File'
		when [BackupType]='P' then 'Partial'
		when [BackupType]='Q' then 'Differential partial'
		when [BackupType] is NULL then 'NULL'
		else [BackupType]
	end [MissingBackups]
	,t1.state_desc [DBState]
	,count(1) [databases]
	,case 
		when min(t1.days)=max(t1.days) then convert(varchar(10),min(t1.days))
		else convert(varchar(10),min(t1.days))+' - '+convert(varchar(10),max(t1.days)) 
	end [DaysDelay]
	,(reverse(stuff(reverse((select quotename([database_name]) + char(10) as 'data()' from data2 t2 where t1.[BackupType]=t2.[BackupType] and t1.state_desc=t2.state_desc for xml path('') )),1,1,''))) [dbs]
from data2 t1 
group by [BackupType], t1.state_desc 

union all 

select 
	'Remote FAILED!'
	,'SPN/Timeout/etc'
	,NULL
	,NULL
	,'Unable to check backups on remote replica!'
where @remote_failed=1