SQL Backups Report
This is a simple report to tell you the status of your SQL Server backups.
We have quite a few SQL servers at athena health and making sure they’re getting backed up is of huge importance to us, as it is for anyone. Our old report relied on SMO but was falling over because of some mirrored databases and our new Availability Group, situations that the original script wasn’t designed to handle. The script was also just not taking advantage of PowerShell and it’s capabilities and had a lot of performance red flags (tons of string concatenation, etc). It was time for a rewrite!
First thing I wanted to get away from using SMO, which requires installation on the workstation where it’s running. I try to avoid that whenever possible and since I knew I could get all of the information using T-SQL I decided to do that. That meant leveraging the Invoke-SQLQuery function yet again. As I recently mentioned here, not a single day goes by at work where I don’t use this function!
To make this all work I run several queries against the server to determine if a database is mirrored or secondary in a Availability Group. If they are than the database is skipped an no reporting is done on it.
The real meat of the script comes from this query:
It basically gets all the information I need, limiting the output to just the very last backup taken.
Overall, it’s a complex script that’s got a lot of moving parts so I won’t go into the dirty play by play that I typically go, but I would like to mention that I also use the Set-ColorHTMLRowsByColumn function to logically group the servers together in the report.
I’ve put the script on Git so if you have some ideas for the script feel free to fork it and put them in!