Robert Davis (5/22/2009)
That's great if you only have one or two SQL Servers. Would you do the same if you have 30 servers or 300 servers or 3000 servers?I think the script would be more useful if you could pass in a threshold value and have it only send the email if the backup exceeded the specified threshold.
Here is how I do it:
I have a procedure on every server that collects its own backup information every day. Then I have a centralized ops server that captures the data from each server and combines them in one place. A job then sends me an alert telling me which databases on which servers have exceeded the backup threshold highlighted in red. For the servers with no databases that exceed the threshold, it shows the server name with its oldest database backup highlighted in green.
So I get one unified report for all servers.
Thanks Robert, I see your point. Yes its true that I have only one Production server active at one time (Active/Passive Cluster). However in a setup with more than one server, hosting different databases (Assuming that they are linked servers) a simple change to the select statement would do the job. If you look closely you will notice that I am inserting into a table variable. Therefore multiple inserts could also be done prior to composing the email. A where clause on these selects would ofcourse be able to filter as per requirement.
However I must say I pity the DBA who has to monitor even 30 servers with different databases on each server, let alone 3000. My hats off to you for being able to do so.