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
This is exactly why we wrote the stored procedure sp_BlitzBackups. Okay, so it can do a little bit more than centralize backup history, but you may not care about all that.
It's totally free, and part of our First Responder Kit.
An example of how to use it to push backup history is in the documentation.
EXEC sp_BlitzBackups @PushBackupHistoryToListener = 1, -- Turn it on!
@WriteBackupsToListenerName = 'AG_LISTENER_NAME', -- Name of AG Listener and/or Linked Server
@WriteBackupsToDatabaseName = 'FAKE_MSDB_NAME', -- Fake MSDB name you want to push to. Remember, can't be real MSDB.
@WriteBackupsLastHours = -24 -- Hours back in time you want to go
I know it says stuff about AGs in there, but you can push data across a Linked Server. It doesn't have to be a an AG Listener.
If you have any questions or run into any bugs, you can get support at the GitHub repo.
Hope this helps!
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.
