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

  • You are looking for a reporting solution for your backup status, or take backup in 250 servers? Why do you need sysadmin privilege to pull backup information from msdb? Did you mean SSRS does not work? – SqlWorldWide Oct 9 '17 at 20:56
  • Yes correct reporting solution for your backup status – BeginnerDBA Oct 9 '17 at 21:05
  • using powershell to loop through all the servers. You don't need sysadmin to read this information. – James Oct 9 '17 at 21:08

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!

  • @Erik.. I loved the way this is designed, Very helpful. was testing in DEv and looks good, Now the problem is how do i get this working to get data from 100 other servers from their MSDB, not using linked server. we are not suppose to use linked servers. Sorry in case i got something wrong. Please suggest, thanks – BeginnerDBA Oct 18 '17 at 21:10

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
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,
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,
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.

  • @SqlWorldWide--> Thanks, when i run above query it always shows * seconds for full backups. What could be the reason – BeginnerDBA Oct 10 '17 at 14:11
  • Also, the script shows the total size of a backup file everytime for each backup file. For example in my case for 20 TB database , we have 8 backup files it shows the size of 8 files as 4 TB, which seems not correct – BeginnerDBA Oct 10 '17 at 14:18
  • @BeginnerDBA try the modified script for your fist issue of * seconds. For second issue I did take into account for striped backup. I have to add group by statement. For now you can add a distinct to avoid the issue. All other columns have the same data. – SqlWorldWide Oct 10 '17 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.