Automating SQL Server database backup checks – Part 2

Following on from Part 1, (wow, over 3 years ago!) now we get to look at how I run SQL commands from PowerShell and use it to generate a daily SQL backup report.

At a high level, this is what we’re going to do:

  • Get a list of all servers and loop through them.
  • Check each server to see if it’s running SQL Server, and get the instance name(s).
  • Run a batch file that contains our SQL query (to get backup history) passing on parameters we pass through to it, and save the results to a delimited text file.
  • Import the text, parse and output to your preferred format.

To start, install a copy of SQL Management Studio on the computer you’re going to run this script from.  We’re going to call sqlcmd.exe later, which is included when you install the SQL tools.

Create a file called c:\scripts\sqlbackups.bat and add the below, which includes our previously constructed query with a few other bits and pieces wrapped around it, like this:

Should you want to modify the above script, here’s a brief explanation of the switches used (case-sensitive):

  • -S (specify the server\instance to run the query on)
  • -E (create s a secure connection, using integrated AD credentials to run the command)
  • -Q (to specify a SQL query)
  • -o (outputs the results to the file name specified)
  • -h (removes the header lines in the output)
  • -s (defines the delimiter used to separate fields in the results – “^” is obscure enough to not be confused with genuine returned results)
  • -W (removes trailing spaces)


Getting sqlcmd.exe to work across your network may/may not work out of the box.  Firewall ports being blocked is but one challenge (there are a potentially many).  Maybe test the above before proceeding.


Here’s the PowerShell script that you would probably run every day (via a Scheduled Task):


After you run the above, you’re going to end up with a bunch of files in c:\scripts\sqlbackups that look like this:


You can then use another PowerShell script to pick up these files and parse them into a readable HTML report or email format.  Remember also that the default instances were blanked out in the above script, so you will have some file names that are just the server name and the “^” character without any instance name after it.  Eg:  c:\scripts\sqlbackups\PRODSQL^.txt

Here’s the bare bones starter script to pick up the files and to start processing them:



SQLcmd syntax –

Leave a Reply

Your email address will not be published. Required fields are marked *