Mail me the backups results

Mail me the backups results

05, Jun Klaas

In this article I show how to send the backup states of all your databases in an e-mail. Of course, all automated with PowerShell.

Case

All DBA's know how important it is to check database backups. There are a lot of strategies we can follow and some third party software to handle this. Maybe you have alerts on backup jobs, or you click away in SSMS for the best part of the morning?
What if our manager wants to be updated on the state of our backups? Or if we do get some days off and our replacement is new to SQL Server and has never heard of this PowerShell thing?
Maybe we just want this for ourselves :-)

Solution

We will write a script that gets the information about the last backups on the SQL Instances of interest, and send the results to our manager or stand in by e-mail.
Like this:

backupsMail

Steps

Roughly, this is what we want to do:

  1. list our SQL Servers
  2. ask every SQL Server what its backup status is
  3. convert the answers into a nice colourful table
  4. send this table in an e-mail to whom it may concern

First some preparations

First of all we will import the dbatools module. In PowerShell V3 and higher, there is auto-loading. That means a module will implicitly be loaded the moment you call a function in it. In a script we have to import explicitly, however. 

Import-Module dbatools

Next, we need some information to make it all work together, like

  • the date, to add in the e-mail subject
  • e-mail addresses
  • a mail server
  • some CSS to define the style of the e-mail body 
$today = get-date -Format "dd MMM yyyy"

$To = '[email protected]', '[email protected]', '[email protected]'
$MailServer = 'mailout.mycompany.com'

$style = @"
<style>
H2{color:blue}
TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse}
TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:seagreen;font-size:16;color:white}
TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;color:blue;font-size:14}
</style>
"@

Gather SQL Instances

I described some methods to collect our SQL Instances in an article Gather Computers.
Let's assume we have a txt file that contains the SQL Instances we want to monitor. We get them out with a Get-Content: 

$MyInstances = Get-Content C:\Scripts\SQL\MyInstances.txt

How are your backups doing?

To ask the SQL Instance information about the backup status, we will use the Get-DbaLastBackup function from the dbatools module. You can read all about it on the dbatools website. 

$LastBackups = Get-DbaLastBackup -SqlInstance $MyInstances | 
 convertTo-Html -Head $style -Property server,database,lastfullbackup,lastdiffbackup,lastlogbackup,status

I think you can deduct what we did by reading this statement.
$LastBackups = We will assign the output to a variable because we're not finished with it yet.
Get-DbaLastBackup ... We execute the dbatools function that gives us the last backups from the SQL Instances we included in the $MyInstances variable.
| ConvertToHtml well, this converts the returned objects into HTML.
-Head $Style this parameter accepts HTML code to put in the header. We only defined some styles in our $Style variable.
-Property ... lists the properties of the returned objects that will be included in the HTML table.

Construct the body

First a header

We start with a HTML header2: 

$body = "<h2>Backup Status:</h2>"

We literally type the HTML code and put that in a variable.

Some replace magic

This is the nicest part of the script. We create a hashtable that has 4 keys. Those keys are a bit of text that can appear in the HTML we generated before. In this case they are the possible values of the [Status] property:

  • 'No Full or Diff Back Up in the last day'
  • 'No Log Back Up in the last hour'
  • 'New database, not backed up yet'
  • 'OK'

The values of those keys are the same text bits, but wrapped in a 'bgcolor' tag. What?
Well, in the HTML we have, those text bits are enclosed between tags: <td>OK</td>.
We'll replace that with: <td bgcolor="#00ff00">OK</td>. This particular table cell will be shown with a green background then.
The statements that make it all happen are: 

$colorTags = @{'No Full or Diff Back Up in the last day' = ' bgcolor="#ff0000">No Full or Diff Back Up in the last day<';
                    'No Log Back Up in the last hour' = ' bgcolor="#ff6600">No Log Back Up in the last hour<';
                    'New database, not backed up yet' = ' bgcolor="#9900ff">New database, not backed up yet<';
                   'OK' = ' bgcolor="#00ff00">OK<'}

$colorTags.Keys | foreach { $LastBackups = $LastBackups -replace ">$_<",($colorTagTable.$_) }

This may be the hardest part to read in the entire script. We take all the strings we added as keys in the hash table, $colorTags.Keys and pass them to the pipeline into our $LastBackups, which is our HTML code. Every occurence of this string between '><', ">$_<" will be replaced by it's corresponding value ($colorTagTable.$_).

Notice: the [status] column is just an arbitrary evaluation of the last backup times the dbatools team decided to include. Your tresholds and requirements may be totally different. In that case, you can ignore the [status] column and calculate your own.

Add the table to the HTML body

This is a lot easier: 

$body += $LastBackups

Send the E-mail

The Send-MailMessage command is delivered with PowerShell itself, so, need to install it. It's ready for us. 

Send-MailMessage -To $To -Subject "SQL Backups on $today" –From "[email protected]" -SmtpServer $MailServer -Body $body -BodyAsHtml

We use some of the variables we declared in the beginning of the script. There are more options available, see msdn.

The complete script

Import-Module dbatools

$MyInstances = Get-Content C:\scripts\SQL\MyInstances.txt
$today = get-date -Format "dd MMM yyyy"

$To = '[email protected]', '[email protected]', '[email protected]'
$MailServer = 'mailout.mycompany.com'

$style = @"
<style>
H2{color:blue}
TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse}
TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:seagreen;font-size:16;color:white}
TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;color:blue;font-size:14}
</style>
"@

$LastBackups = Get-DbaLastBackup -SqlInstance $MyInstances | 
 convertTo-Html -Head $style -Property server,database,lastfullbackup,lastdiffbackup,lastlogbackup,status

$body = "<h2>Backup Status:</h2>"
$colorTagTable = @{'No Full or Diff Back Up in the last day' = ' bgcolor="#ff0000">No Full or Diff Back Up in the last day<';
                    'No Log Back Up in the last hour' = ' bgcolor="#ff6600">No Log Back Up in the last hour<';
                    'New database, not backed up yet' = ' bgcolor="#9900ff">New database, not backed up yet<';
                   'OK' = ' bgcolor="#00ff00">OK<'}
$colorTagTable.Keys | foreach { $LastBackups = $LastBackups -replace ">$_<",($colorTagTable.$_) }
$body += $LastBackups

Send-MailMessage -To $To -Subject "SQL Backups on $today" –From "[email protected]" -SmtpServer $MailServer -Body $body -BodyAsHtml

Finishing touch

Now we have the complete script, we can choose to deliver it as is to a coworker who can execute it ad hoc. Maybe our manager prefers to receive the e-mail daily at a fixed time. In that case, we create a scheduled task.
It all depends on the customs and needs of your company. Keep in mind that permissions on the SQL Instances and the e-mail server are needed too. If a recipient does not have those permissions, he or she will not get the required information.

To be continued

Now you have one example of how you can send SQL audits in an e-mail.

You can alter this in many ways. Maybe you want to show not only the date, but also the time the script was executed. Or you could add other tests, like some configuration settings or security information or available diskspace,... It's easy to add or replace the current variables.
You can add more CSS style, either to the HTML header, or in the table cells, like bold or italic or whatever.
You can use a different method to list your SQL Instances.

Let the creativity flow.

Previous Post Next Post

[email protected]