The Surly Admin

Father, husband, IT Pro, cancer survivor

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:

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!

SQLBackupReport1

New-SQLBackupReport

Advertisements

June 8, 2015 - Posted by | MS SQL, PowerShell | ,

11 Comments »

  1. 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?

    Comment by Wayne | July 3, 2015 | Reply

    • The text file should be one line, one server or server/instance:

      server1
      server2
      server\win

      Comment by Martin9700 | July 5, 2015 | Reply

  2. 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?

    Comment by ctipton | February 4, 2016 | Reply

  3. 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?

    Comment by ctipton35759 | February 4, 2016 | Reply

    • 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.

      Comment by Martin9700 | February 4, 2016 | Reply

      • The server does have sql installed, and i can manage it from ssms. Connection string on web apps uses 1433. I am stumped.

        Comment by ctipton35759 | February 4, 2016

      • Are you running SSMS remotely or on the server? You may have to activate remote TCP connections from the SQL Configuration Manager

        Comment by Martin9700 | February 4, 2016

      • I am running it remotely…

        Comment by ctipton35759 | February 4, 2016

  4. […] asynchronous nodes yet.  There are also a couple of reports I want to include in the module for backups and job […]

    Pingback by Announcing the PS.SQL Module « The Surly Admin | February 19, 2016 | Reply

  5. 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?

    Comment by Kent Saunders | December 21, 2016 | Reply

    • 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).

      Comment by Martin9700 | December 28, 2016 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: