select COUNT(name) AS [Count Databases] from sys.sysdatabases 
where dbid>4

;with logs as 
(
select
	d.name
	, case when lssd.secondary_database = d.name then 1 else 0 end as IsSecondary
from 
	sys.databases d
left join msdb..log_shipping_secondary_databases lssd on d.name = lssd.secondary_database
) 


, backups as 
(
select 
	ROW_NUMBER() over(partition by bs.database_name order by bs.backup_finish_date desc) as RowNumber
	, bs.database_name as DatabaseName
	, cast(bs.backup_finish_date as date) as LastBackupDate
	, cast (bs.backup_start_date as date) as StartTime
	, cast (bs.backup_finish_date as date) FinishTime
	,CAST(CAST(bs.compressed_backup_size /1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Backup Size(MB)]
	, bsmf.physical_device_name BackupPath
from
	msdb..backupset bs
join msdb..backupmediafamily bsmf 
ON bs.media_set_id = bsmf.media_set_id
where bs.type in ('D','I')
)

select
	b.DatabaseName
	,b.LastBackupDate
	,b.StartTime
	,b.FinishTime
	,b.[Backup Size(MB)]
	,b.BackupPath
	,l.IsSecondary
from
	backups b
join logs l 
ON b.DatabaseName = l.name
	where b.RowNumber = 1
order by LastBackupDate desc