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:

sqlcmd -S %1\%2 -E -Q "SELECT sys.databases.name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date&nbsp;FROM sys.databases left JOIN msdb.dbo.backupset ON sys.databases.Name = msdb.dbo.backupset.database_name WHERE msdb.dbo.backupset.type = 'D' OR msdb.dbo.backupset.type IS NULL&nbsp;GROUP BY sys.databases.name" -o "c:\scripts\sqlbackups\%1^%2.txt" -h-1 -s"^" -W

sqlcmd -S %1\%2 -E -Q "SELECT sys.databases.name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM sys.databases left JOIN msdb.dbo.backupset ON sys.databases.Name = msdb.dbo.backupset.database_name WHERE msdb.dbo.backupset.type = 'D' OR msdb.dbo.backupset.type IS NULL GROUP BY sys.databases.name" -o "c:\scripts\sqlbackups\%1^%2.txt" -h-1 -s"^" -W

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):

# Delete old results (if running every day)
get-childitem "c:\scripts\sqlbackups" | remove-item;

# Load the Active Directory PowerShell module
import-module activedirectory;
 
# get all servers from AD
$servers = get-adcomputer -filter {operatingsystem -like "*server*"} | sort name;

# Loop through each server in the environment
foreach ($server in $servers)
{
    # Check if server can be pinged
    if(test-connection -cn $server.name -quiet -count 1 -ea 0)
    {
        # Check if server is running sql services
        $sqlservices = $null;
        $sqlservices = get-service -displayname "sql server (*" -computername $server.name;

        # If SQL instances are found
        if ($sqlservices -ne $null)
        {
            # Loop through each instance found
            foreach ($sqlservice in $sqlservices)
            {
                # Format instance name for batch file query - must be blank for default instance
                if ((($sqlservice.displayname).split("(")[1]).split(")")[0] -eq "MSSQLSERVER")
                {
                    # Default instance found, set it to blank
                    $instancename = "";
                }
                else
                {
                    # Named instance found
                    $instancename = (($sqlservice.displayname).split("(")[1]).split(")")[0];
                }

                # Call batch file to run sqlcmd and query backups
                $process = "/c c:\scripts\sqlbackups.bat " + $server.name + " " + $instancename;
                start-process "cmd.exe" $process -NoNewWindow -wait;
            }
        }
    }
}

# Delete old results (if running every day)

get-childitem "c:\scripts\sqlbackups" | remove-item;

# Load the Active Directory PowerShell module

import-module activedirectory;

# get all servers from AD

$servers = get-adcomputer -filter {operatingsystem -like "*server*"} | sort name;

# Loop through each server in the environment

foreach ($server in $servers)

{

# Check if server can be pinged

if(test-connection -cn $server.name -quiet -count 1 -ea 0)

{

# Check if server is running sql services

$sqlservices = $null;

$sqlservices = get-service -displayname "sql server (*" -computername $server.name;

# If SQL instances are found

if ($sqlservices -ne $null)

{

# Loop through each instance found

foreach ($sqlservice in $sqlservices)

{

# Format instance name for batch file query - must be blank for default instance

if ((($sqlservice.displayname).split("(")[1]).split(")")[0] -eq "MSSQLSERVER")

{

# Default instance found, set it to blank

$instancename = "";

}

else

{

# Named instance found

$instancename = (($sqlservice.displayname).split("(")[1]).split(")")[0];

}

# Call batch file to run sqlcmd and query backups

$process = "/c c:\scripts\sqlbackups.bat " + $server.name + " " + $instancename;

start-process "cmd.exe" $process -NoNewWindow -wait;

}

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:

# Get all DB reports
$sqlbackupfiles = get-childitem C:\scripts\sqlbackups

# Loop through each file
foreach ($sqlbackupfile in $sqlbackupfiles)
{
    # Format hostname and instance name from file name
    $hostname = ($sqlbackupfile.name).split("^")[0];
    $instance = (($sqlbackupfile.name).split("^")[1]).split(".")[0];

    # Format default instance name
    if ($instance -eq "")
    {
        $instance = "MSSQLSERVER";
    }

    $instance =  $hostname + "\" + $instance;
    write-host $instance;

    # Get contents of file
    $sqlbackups = get-content $sqlbackupfile.fullname;

    # Loop through each line of the file
    foreach ($database in $sqlbackups) {

        ##########################################
        ###### ADD PARSING/OUTPUT CODE HERE ######
        ##########################################
    }
}

# Get all DB reports

$sqlbackupfiles = get-childitem C:\scripts\sqlbackups

# Loop through each file

foreach ($sqlbackupfile in $sqlbackupfiles)

{

# Format hostname and instance name from file name

$hostname = ($sqlbackupfile.name).split("^")[0];

$instance = (($sqlbackupfile.name).split("^")[1]).split(".")[0];

# Format default instance name

if ($instance -eq "")

{

$instance = "MSSQLSERVER";

}

$instance = $hostname + "\" + $instance;

write-host $instance;

# Get contents of file

$sqlbackups = get-content $sqlbackupfile.fullname;

# Loop through each line of the file

foreach ($database in $sqlbackups) {

##########################################

###### ADD PARSING/OUTPUT CODE HERE ######

##########################################

}

References

SQLcmd syntax – https://msdn.microsoft.com/en-us/library/ms162773.aspx

HKEY_LOCAL_MACHINE

Every problem is an opportunity.

Automating SQL Server database backup checks – Part 2

Leave a Reply Cancel reply