Create graphic report showing databases backed up and success stats
-
I work for a company with a small IT dept. I’m an experienced Sys Admin, but I don’t have much SQL knowledge. I’ve become the accidental DBA. I’ve been tasked with sending out a monthly report that shows our success rate for backing up all of our SQL Server databases. We use several versions of SQL Server, including SQL Server 2000, 2012, 2016 and 2019. (I believe those are the correct version names.) My manager would like the report to look professional and include graphs or pie charts. He doesn’t want it to just be a document that I type up. Can SQL Server Standard Reports be used to combine data from multiple servers and databases? How do larger companies create these types of reports? I know I won’t get approval to spend thousands of dollars on monitoring software. Also, when I searched online, the monitoring software focused on performance and didn’t mention creating reports. Does anyone have any suggestions of how to use the SQL Agent or local SQL log files to create reports worthy of sending to upper management? I’d appreciate any advice or suggestions of resources I could look at.
-
This was removed by the editor as SPAM
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
To be honest, you're post looks like a typical two part spam, the second part of which has already been deleted as spam.
Since you're new on this forum, if your not part of the spam nonsense, please post back with some measures that your boss would like to see on the charts. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A reply to my post was deleted as spam. My post was not marked as spam. I want the reports to show how many databases I’m backing up, time taken to finish backups, and the percentage of successful backups vs. failures. I can assure you that this is not spam. I am a real person asking a legitimate question. If there is anyone else willing to reply in a more civilized tone, I would appreciate it.
-
Years ago I was a NetBackup administrator. I can’t recall if we paid for a feature of NetBackup or a third party app. The feature provided a dashboard that polled all of the client servers and allowed me to create and send out reports. The reports included text and graphics showing how many servers I was backing up and the success rates of the server backups. I could include bandwidth being used, bottlenecks, etc. I’m wondering if there are any tools like that available for SQL Server. I know there must be, I’m just at a smaller company now with a smaller IT staff and smaller budget. I prefer to search for information myself or to speak with colleagues I know will guide me to trustworthy sources. Unfortunately, I’m in a position where I’m asking strangers on a forum I’m new to for advice. At least the first reply I saw wasn’t as cruel as the one I received for my t-sql coding question I posted on stack overflow. Seeing how thick-skinned I need to be, I may as well ask my questions on Twitter and expect to get dragged through the mud.
- got2be wrote:
A reply to my post was deleted as spam. My post was not marked as spam. I want the reports to show how many databases I’m backing up, time taken to finish backups, and the percentage of successful backups vs. failures. I can assure you that this is not spam. I am a real person asking a legitimate question. If there is anyone else willing to reply in a more civilized tone, I would appreciate it.
Thanks for the response. And my tone was civilized. Let me see what we can do.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I believe that NetBackup actually provided the monitoring dashboard you speak of. What are you using for backups now? Are they SQL "native" backups or are you using some other tool(s) to do your backups. And, to ask the question, how many servers are you talking about?
The 2012+ servers shouldn't be much of an issue no matter which route you go. I'm not so sure about the 2000 servers and I don't have one that I could do any testing on. Also, are all the servers within the same domain?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's been a long time since I've looked at backup dashboards and monitoring software. All of the ones I've looked at in the last hour are pretty disappointing. LiteSpeed, which is a backup application, has some good reporting but it's all in a list, which your boss says he doesn't want. Redgate's SQL Monitor has something close to what you're looking for (see the following URL) but it's a bit expensive. It DOES have more things that it does other than just monitoring backups... a whole lot more.
I did look at RedGate's backup tool at the following site but they didn't offer much information about any dashboard other than to state "User-friendly GUI for the centralized management of backup and restore activities" near the bottom of the page at the following link.
https://www.red-gate.com/products/dba/sql-backup/
Another one (Idera) seems to have a backup "dashboard" and you can see it at time-stamp 4:52 in the following 'tube on the subject. It has some of the graphics you spoke of.
https://www.idera.com/resource-center/videos/sql-safe-overview/
One piece that I did find appears to be strictly a reporting tool for backups and not just SQL Server backups. It might be more what you're looking for. I wasn't real impressed with their short flick at the following URL but the narrative seemed to support what you might be looking for. Here's the link...
https://www.rocketsoftware.com/products/rocket-servergraph/backup-monitoring-software
To be honest, I've never found any backup "dashboard" that suites my needs. For example, I recently needed a graph to show the drop in effective throughput rates and when they hit the floor because my 7TB/3 hour backups suddenly started taking 4 to 19 hours. I queried a couple of the tables in the MSDB database, dropped the results into a spreadsheet and graphed the results. I'm considering making a stored procedure out of what I did and then making a "refresh-able" spreadsheet where I can fill in a start and end date, click a button, and have the spreadsheet do all the work for me.
And, no... there's nothing "native" in SQL Server that would produce such an output.
Sorry that's not much help but I've not yet found one to do the kind of analysis in chart for that I needed or that it sounds like you need.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's one more that shows some promise but it's not apparent as to whether or not it includes SQL Server backups.
https://www.bocada.com/product/backup-monitoring/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And, in case you're wondering what the chart looked like that I needed (and sound like what you want) here's what I ended up making. The Red lines show the dismal drop in thru-put on Friday nights and the Yellow lines show the drops on other nights. The overall chart showed a pretty nasty change after a Windows Update on the 28th of August for one of the production databases that weighed in at about 1.6TB at the time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Our servers are being backed up with Rubrik. The Sys Admin who does those backups used to do separate database backups on each server, but wants to save storage space. He will only backup the servers as images from now on.
Our servers are in one domain, and we have around 50 of them. We have more hosted in Azure - but I'm concentrating on the on-prem backups right now.
Thank you for all the links to dashboard and reporting tools. I want to take my time to read through them. I just finished working for the day and haven't had a chance yet.
I like the chart that you created. That would be very helpful to prove to other users that their activity is interfering with backups that are running at night.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply