SQL Backups Report
This is a simple report to tell you the status of your SQL Server backups.
New-SQLBackupReport
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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT bk.database_name AS Name, | |
| bk.recovery_model AS RecoveryModel, | |
| bk.backup_finish_date AS LastBackupDate, | |
| bk.Type, | |
| bk.backup_size AS LastBackupSize | |
| FROM msdb.dbo.backupset AS bk | |
| INNER JOIN ( | |
| SELECT database_name, | |
| MAX(backup_finish_date) AS LastBackupDate, | |
| Type | |
| FROM msdb.dbo.backupset | |
| GROUP BY database_name,Type | |
| ) AS bkmax | |
| ON bk.database_name = bkmax.database_name and | |
| bk.backup_finish_date = bkmax.LastBackupDate and | |
| bk.type = bkmax.type |
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!
New-SQLBackupReport
11 Comments »
Leave a Reply
Me
LinkedIn
The Surly Admin 
Hi, Love the idea of this script.
Having issues with the Serverlist.txt, the servers are in a separate txt file in the same directory such as;
server1, server2,server3
Does it also need to be the DB instance such as;
server\win, servers\test
I have also tried “server1″,”server2”
but none seem to be detected?
The text file should be one line, one server or server/instance:
server1
server2
server\win
I continuously get Exception calling “Fill” with “1” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)”
Any ideas?
I constantly get Exception calling “Fill” with “1” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)”
Any ideas?
You’re either pointing at a server that doesn’t have SQL installed on it, or the script can’t communicate with the server over port 1433. Either firewall, alternative port configuration, etc.
The server does have sql installed, and i can manage it from ssms. Connection string on web apps uses 1433. I am stumped.
Are you running SSMS remotely or on the server? You may have to activate remote TCP connections from the SQL Configuration Manager
I am running it remotely…
[…] asynchronous nodes yet. There are also a couple of reports I want to include in the module for backups and job […]
I am having issues when severlist contains a server with a named instance.
Here is what I receive WARNING: 12/21/2016 15:14:24: Unable to ping DCXSCCMSQL\WSUS, skipping server
Can you give some direction?
I’ve updated the link to go to Surly.PowerShell.SQL.Tools where there’s an updated version of Get-SQLBackupStatus and New-SQLBackupReport. Supports named instances (actually has for awhile, just didn’t update the link on the blog post).