skip to main content

Monitor SQL Server backup jobs with PowerShell

As part of my PowerShell adventures, I have created a shell script to monitor database backup jobs and email a report to the team daily.

These jobs are created by the DBAs on SQL Server databases. So all I know is the name of the job and the people to notify. This shell script can be customized for any type of job, but I am using it for differential backup jobs.

Database query

The meat of the entire shell script is in the database query. SQL Server jobs are stored in system database msdb. This database has, among others, two tables that hold the information necessary for generating our report - sysjobhistory and sysjobs.

So we write the query as follows joining these two tables.

SELECT hist.SERVER AS [Server],
    job.[name] AS [JobName],
    CASE 
        WHEN hist.message LIKE '%differential%'
            THEN 'DIFF'
        ELSE 'FULL'
        END "BackupType",
    CASE hist.run_status
        WHEN 0
            THEN 'Failed'
        WHEN 1
            THEN 'Success'
        WHEN 2
            THEN 'Retry'
        WHEN 3
            THEN 'Canceled'
        WHEN 4
            THEN 'In progress'
        END "RunStatus",
    stuff(stuff(replace(str(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS [RunDuration],
    convert(DATETIME, convert(VARCHAR(8), run_date) + ' ' + stuff(stuff(right(1000000 + run_time, 6), 3, 0, ':'), 6, 0, ':')) AS LastRunTime
FROM msdb..sysjobhistory hist
INNER JOIN msdb..sysjobs job
    ON hist.job_id = job.job_id
WHERE job.enabled = 1
    AND job.name LIKE '%[_]backup%'
    AND hist.step_id = 1
    AND DATEDIFF(dd, CONVERT(VARCHAR(8), hist.run_date), GETDATE()) <= 1

The required criteria needs to be understood.

job.enabled = 1
means jobs that are active

job.name LIKE '%[_]backup%'
means jobs with names that end with _backup

hist.step_id = 1
is a way to restrict records to one row per job. We could also use (job outcome) step to achieve this.

DATEDIFF(dd, CONVERT(VARCHAR(8), hist.run_date), GETDATE()) <= 1
means jobs that ran since yesterday.

Thanks Vamsy for the pointing me to system database msdb.

Now that we have our script ready, let us complete the shell script.

Shell script

The step by step breakdown:

  1. Create a log file
  2. Fetch database server list
  3. Collect results
  4. Format the result set
  5. Create a style tag
  6. Create HTML report
  7. Notify team

Create a log file

Since this is a daily job, we need to be able to identify the log file by looking at the filename. Though the filename may seem verbose, I do this, since the files may be moved to another location later on and the context is lost unless we put in some extra effort on metadata.

$logsfolder = "logs\failedbackups\"
$filedate = get-date –uformat %m%d%Y-%H%M%S;
$logfile  = $logsfolder+"failedjob-report-"+$filedate+".log"

This will give me the following filename

failedjob-report-07072018-192657.log

Fetch database server list

I use this local path, (local to the server), for storing configuration for all my scripts. Use Get-Content to get all content of the dbs.txt file.

$cfg="C:\Documents and Settings\banimesh\ps\config"
$dbservers = @(gc $cfg\dbs.txt)

Collect results

Once we have the list of database servers, we iterate through it, execute the database query and get the results back into a datatable.

A DataTable and SQLConnection in PowerShell. I don't know about you, but that is so cool. To be able to use full .NET framework inside PowerShell is definitely a winning approach, should you feel the need to do so. You might ask then ask why, and rightly so, use PowerShell instead of a C# program. That topic is itself a blog post or article someone somewhere will write or has written already, but succinctly, PowerShell is a shell scripting language and automation framework. That is it. With .NET framework at its disposal, you just accessed its god mode. Please, that someone, write an article about this, so I don't have to explain it. Thank you.

One example I would usually give is, if you want a list of environment variables, you would simply type gci env: on the PowerShell prompt. I can imagine there being a simple environment API to access with a C# program, but the time to open Visual Studio and create a working solution is not worth your while. This explanation is only for people who don't get the why of PowerShell.

Sorry for the digression.

Then, similar to an ADO.NET piece of code, we create an SQLConnection and open it,

$dataTable = new-object "System.Data.DataTable"
$conn = new-object System.Data.SqlClient.SqlConnection "server=$server;database=msdb;Integrated Security=sspi"
$conn.Open()

create an SQLCommand and pass it our database query,

$sqlCmd = $conn.CreateCommand()
$sqlCmd.CommandText = $(get-content $cfg\report2.sql)

and finally create an SQLDataReader to hold the result set.

$reader = $sqlCmd.ExecuteReader()

Then load the result set in the datatable and close the connection.

$dataTable.Load($reader)
$conn.Close()

Then append each datatable to a datatable array, $datatables.

$dataTables += $dataTable

Format the result set

We pipe the datatable array $datatables to Format-Table utility to create a PowerShell table object according to the structure of our result set, and dump it into the log file.

-auto parameter, short for AutoSize, automatically sizes your table, so it looks nice.

Now that the content of our report is ready, we move on to building a HTML report.

Create a style tag

We will create a style tag and append contents of a report.css into it. This is so we can can embed the style tag inside our HTML report.

$styleTag = "<style>"
$styleTag = $styleTag + @(gc $cfg\report.css)
$styleTag = $styleTag + "</style>"

Create HTML report

Now we transform the $datatables object in three ways.

  1. Convert into a PowerShell object so that it can be further transformed.
  2. Sort the object by RunStatus and then by Server, so that most recently run job is seen first.
  3. Convert this PowerShell object into an HTML table.

This HTML table contains a full boilerplate HTML with a table with its child elements.

$emailbody = $dataTables 
        | select-object Server,JobName,BackupType,RunStatus,RunDuration,LastRunTime 
        | Sort-Object RunStatus,Server 
        | ConvertTo-Html -head $styleTag

-head $styleTag parameter sets the style tag inside the head element of this generated HTML. What!

Notify team

Once the HTML report is ready, we send it using SMTP utils of PowerShell. We check the previously filled log file if there are any errors. If any, then change a few things to catch the recipients attention.

The completed shell script follows.

# 1. Create a log file 
$today = Get-Date
write-host $today
$today = $today.ToString("MM dd yyyy")

$logsfolder = "logs\failedbackups\"
$filedate = get-date uformat %m%d%Y-%H%M%S;
$logfile  = $logsfolder+"failedjob-report-"+$filedate+".log"
$psf="C:\Documents and Settings\banimesh\ps\"

# 2. Fetch database server list
$cfg="C:\Documents and Settings\banimesh\ps\config"
$dbservers = @(gc $cfg\dbs.txt)

$dataTables=@()

# 3. Collect results
foreach($server in $dbservers)
{
    $dataTable = new-object "System.Data.DataTable"
    $conn = new-object System.Data.SqlClient.SqlConnection "server=$server;database=msdb;Integrated Security=sspi"
    $conn.Open()
    $sqlCmd = $conn.CreateCommand()
    $sqlCmd.CommandText = $(get-content $cfg\report2.sql)
    $reader = $sqlCmd.ExecuteReader()
    $dataTable.Load($reader)
    $conn.Close()
    $dataTables += $dataTable
}

# 4. Format the result set
$dataTables | ft Server,JobName,BackupType,RunStatus,RunDuration,LastRunTime -auto
         >> $psf\$logfile

# 5. Create a style tag
$styleTag = "<style>"
$styleTag = $styleTag + @(gc $cfg\report.css)
$styleTag = $styleTag + "</style>"

# 6. Create HTML report
$emailbody = $dataTables 
                | select-object Server,JobName,BackupType,RunStatus,RunDuration,LastRunTime 
                | Sort-Object RunStatus,Server 
                | ConvertTo-Html -head $styleTag

# 7. Notify team
$emailFrom = "notifier@company.com";
$emailTo = "itsupport@company.com";

$cnt = (select-string $psf\$logfile -pattern "failed").count

if($cnt -gt 0)
{
    $subject = "Daily Backup Errors Occurred $($today)";
}
else
{   
    $subject = "Daily Backup Notification $($today)";
}

$msg = New-Object Net.Mail.MailMessage($emailFrom, $emailTo,$subject,$emailBody)
$msg.IsBodyHTML = $true
$smtpServer = "smtpsrv"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

if($cnt -gt 0)
{
    $msg.Priority = [System.Net.Mail.MailPriority]::High;
}

$smtp.Send($msg)

Styling the report

It is not the most visually pleasing, but it will do.

body {
    font-size: small;
}

TABLE {
    width: 90%;
    border: 1px solid #999;
    border-collapse: collapse;
}

TH {
    width: auto;
    border: 1px solid #ccc;
    text-align: left;
    padding: 1px;
    background: lightyellow;
}

TD {
    width: auto;
    border: 1px solid #ccc;
    text-align: left;
    padding: 2px 1px;
}