question

getzeeshan avatar image
getzeeshan 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
sql-server-2008sql-server-2008-r2sql-server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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/
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

getzeeshan avatar image getzeeshan commented ·
Thanks! very much. I will defenitly take a look into this option. --Z
0 Likes 0 ·
getzeeshan avatar image getzeeshan commented ·
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
0 Likes 0 ·
KenJ avatar image KenJ commented ·
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.
0 Likes 0 ·
getzeeshan avatar image getzeeshan commented ·
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
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.