I have a setup of Central Management Servers in my SQL Server and it’s been a big help to me in my everyday database administration tasks. I want to share with you guys my CMS setup as below.
So for across all these servers, and everytime I need to setup a new one, I only have one maintenance plans then I just scale it out to all the target servers. Neat!
One neat script that you can run in your CMS is a quick check of backup history across all your target servers. See my script below;
CAST(CAST(backupset.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ MB’ AS backupsize,
backupset.[type] AS BackupType,
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmedia
ON backupset.media_set_id = backupmedia.media_set_id
It will give you the results as below, then you can just do a scan to see if all your target servers had a successful backup overnight.
If you need to automate this script, you will need to do additional scripting to store the data in a table somewhere that you can export to a report later and then you need to create a scheduled job for this.
I hope this is helpful to you also 🙂