asked
Consolidated backup reports for multiple instances
Hi There, I have one quick questionthat I need help from you DBA's I'm not very experienced with SQL Server but I'm looking for a solution. My question is --> we have 30 different Instances & some named instances on 30 different servers. On a daily basis we get 30 emails of backups being successful/ failed and its tedious for my team to go over each email most of the DBA's end up deleting it. --> All I need is to get ONE consolidated backup report email for all 30 different instances instead of getting 30 emails. --> for this I'm trying to run some queries from one of our central monitoring server - since this is the only Sql server (windows Server) which can communicate to all 30 instances. Please let me know if there is any solution? or any query which is already in place
getzeeshan@gmail.com --Z
For a built-in solution, your best bet would be to use [Central Management Server][1]. I wrote on article on Simple-Talk on how to use this. You should make sure you have Policy Based Management installed. Speaking of PBM, you can look at this [Enterprise Policy Management solution][2]. I recommend using it too. It has a number of other utilities that you'll need for managing your servers. It's technically a third party solution, but it's free and the third party is actually Microsoft, so I don't think it should be that much of an issue. [1]:
https://www.simple-talk.com/sql/backup-and-recovery/centralized-management-tips-for-managing-backups-on-multiple-servers/ [2]:
http://epmframework.codeplex.com/
Thanks! very much. I will defenitly take a look into this option. --Z
I added all the instances which I need to connect and get the backup details in CMS is works fine. I.e. following is what I did. I registered the Main Monitoring server in CMS and then registered all instances those I wanted the backup report from. Then I opened a New Query and when I run the query to check if backups have completed or not and the result was as desired. But how to automate this Query to run on CMS was confusing or rather I would say that I could not understand. Thanks! very much --Zee
If you want to run it automatically, you'll need to put something together in SSIS. You can read the server list from the CMS server, then loop through the servers and run the backup detail query on each server, putting the result in a dedicated table on the CMS. You can then report from that table.
And one more question is the Multi server management... does this create some extra agents or what exactly does this toolk does when we configure this part? do you have some write up on this. Thx! -Z
Here's something I write a couple of years back using PowerShell to do what you need. It is easily implemented and works great. [
http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/02/use-powershell-to-report-sql-server-backup-status.aspx][1] [1]:
http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/02/use-powershell-to-report-sql-server-backup-status.aspx
16 People are following this question.
