Centralized Backup report for SQL server databases

Even though, I've searched couple of them online but still looking if there is a better way to manage the SQL server backups, almost across 250 servers.

Issue is we are not allowed sysadmin login to be created on one server to pull this report from.

Please suggest a link or way to view the health report for sql server backups.

Also SSRS works as we are not supposed to use database mail to sen emails

Use your central repository where you keep information for all servers (Most likely another SQL server which is used as a central repository for DBA stuff) in the firm. Use below tsql query as a start and modify as necessary. You can loop thru all your servers and collect backup history. That can be another tsql script called via SQL Agent job or PowerShell. Then you will need to set up a threshold for each type of backup and get alerted if the threshold exceeds.

For example if you do a full backup once a week, differential every other days, and transaction log at xx minute interval; you can set up thresholds of 168 hours for full backup, differential 24 hours + some buffer, transaction log xx minute + some buffer.

-- Get Backup History for required database
SELECT 
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1048576 AS INT) AS VARCHAR(14))  AS bkSizeMB,
CAST(CAST(s.compressed_backup_size / 1048576 AS INT) AS VARCHAR(14))  AS Compressed_bkSizeMB,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(12)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.backup_start_date>'2017-04-01'  --adjust your date
--Uncomment below lines if you want a one or more type of backup
--AND (s.type='D' OR s.type ='I')
--AND (s.type='L' )
--Uncomment below line if you want to filter by database name
--AND database_name ='security'
ORDER BY backup_start_date DESC, backup_finish_date

As an alternative you can leverage these two PowerShell function to achieve the same result.

• Get-DbaBackupHistory

• Get-DbaLastBackup

StackExchange Database Administrators Q#188050, answer score: 3