6 minute read

In a previous post I showed how easy it is to test your backups using Test-DbaLastBackup

Today I thought I would take it a little further and show you how PowerShell can be used to transmit or store this information in the manner you require

Test-DBALastBackup returns an object of information

SourceServer  : SQL2016N2  
TestServer    : SQL2016N2  
Database      : FadetoBlack  
FileExists    : True  
RestoreResult : Success  
DbccResult    : Success  
SizeMB        : 1243.26  
BackupTaken   : 3/18/2017 12:36:07 PM  
BackupFiles   : Z:\SQL2016N2\FadetoBlack\FULL_COPY_ONLY\SQL2016N2_FadetoBlack_FULL_COPY_ONLY_20170318_123607.bak

which shows the server, the database name, if the file exists, the restore result, the DBCC result, the size of the backup file, when the backup was taken and the path used

Text File

As it is an object we can make use of that in PowerShell. We can output the results to a file

Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 -MaxMB 5 | Out-File C:\temp\Test-Restore.txt  
notepad C:\temp\Test-Restore.txt

01 - out file.PNG

CSV

Or maybe you need a CSV

    Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 -MaxMB 5 | Export-Csv C:\temp\Test-Restore.csv -NoTypeInformation

02 - csv file.PNG

JSON

Maybe you want some json

    Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1| ConvertTo-Json | Out-file c:\temp\test-results.json

06 - json results.PNG

HTML

Or an HTML page

    $Results = Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1
    $Results | ConvertTo-Html | Out-File c:\temp\test-results.html

03 - html.PNG

Excel

or perhaps you want a nice colour coded Excel sheet to show your manager or the auditors

    Import-Module dbatools
    $TestServer = 'SQL2016N1'
    $Server = 'SQL2016N2'
    ## Run the test and save to a variable
    $Results = Test-DbaLastBackup -SqlServer $server -Destination $TestServer
    # Set the filename
    $TestDate = Get-Date
    $Date = Get-Date -Format ddMMyyy_HHmmss
    $filename = 'C:\Temp\TestResults_' + $Date + '.xlsx'
    # Create a .com object for Excel
    $xl = new-object -comobject excel.application
    $xl.Visible = $true # Set this to False when you run in production
    $wb = $xl.Workbooks.Add() # Add a workbook
    $ws = $wb.Worksheets.Item(1) # Add a worksheet
    $cells = $ws.Cells
    $col = 1
    $row = 3
    ## Create a legenc
    $cells.item($row, $col) = "Legend"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $row ++
    $cells.item($row, $col) = "True or Success"
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 10
    $row ++
    $cells.item($row, $col) = "False or Failed"
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 3
    $row ++
    $cells.item($row, $col) = "Skipped"
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 16
    $row ++
    $cells.item($row, $col) = "Backup Under 7 days old"
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 4
    $row ++
    $cells.item($row, $col) = "Backup Over 7 days old"
    $cells.item($row, $col).font.size = 12
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 3
    ## Create a header
    $col ++
    $row = 3
    $cells.item($row, $col) = "Source Server"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "Test Server"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "Database"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "File Exists"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "Restore Result"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "DBCC Result"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "Size Mb"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "Backup Date"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col ++
    $cells.item($row, $col) = "Backup Files"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $Cells.item($row, $col).Interior.ColorIndex = 34
    $col = 2
    $row = 4
    foreach ($result in $results) {
        $col = 2
        $cells.item($row, $col) = $Result.SourceServer
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        $col ++
        $cells.item($row, $col) = $Result.TestServer
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        $col++
        $cells.item($row, $col) = $Result.Database
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        $col++
        $cells.item($row, $col) = $Result.FileExists
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        if ($result.FileExists -eq 'True') {
            $Cells.item($row, $col).Interior.ColorIndex = 10
        }
        elseif ($result.FileExists -eq 'False') {
            $Cells.item($row, $col).Interior.ColorIndex = 3
        }
        else {
            $Cells.item($row, $col).Interior.ColorIndex = 16
        }
        $col++
        $cells.item($row, $col) = $Result.RestoreResult
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        if ($result.RestoreResult -eq 'Success') {
            $Cells.item($row, $col).Interior.ColorIndex = 10
        }
        elseif ($result.RestoreResult -eq 'Failed') {
            $Cells.item($row, $col).Interior.ColorIndex = 3
        }
        else {
            $Cells.item($row, $col).Interior.ColorIndex = 16
        }
        $col++
        $cells.item($row, $col) = $Result.DBCCResult
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        if ($result.DBCCResult -eq 'Success') {
            $Cells.item($row, $col).Interior.ColorIndex = 10
        }
        elseif ($result.DBCCResult -eq 'Failed') {
            $Cells.item($row, $col).Interior.ColorIndex = 3
        }
        else {
            $Cells.item($row, $col).Interior.ColorIndex = 16
        }
        $col++
        $cells.item($row, $col) = $Result.SizeMb
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        $col++
        $cells.item($row, $col) = $Result.BackupTaken
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        if ($result.BackupTaken -gt (Get-Date).AddDays(-7)) {
            $Cells.item($row, $col).Interior.ColorIndex = 4
        }
        else {
            $Cells.item($row, $col).Interior.ColorIndex = 3
        }
        $col++
        $cells.item($row, $col) = $Result.BackupFiles
        $cells.item($row, $col).font.size = 12
        $Cells.item($row, $col).Columnwidth = 10
        $row++
    }
    [void]$ws.cells.entireColumn.Autofit()
    ## Add the title after the autofit
    $col = 2
    $row = 1
    $cells.item($row, $col) = "This report shows the results of the test backups performed on $TestServer for $Server on $TestDate"
    $cells.item($row, $col).font.size = 18
    $Cells.item($row, $col).Columnwidth = 10
    $wb.Saveas($filename)
    $xl.quit()

It looks like this. Green is Good, Red is Bad, Grey is don’t care!

Email

You might need to email the results, here I am using GMail as an example. With 2 factor authentication you need to use an app password in the credential

    Import-Module dbatools
    $TestServer = 'SQL2016N1'$Server = 'SQL2016N2'
    ## Run the test and save to a variable
    $Results = Test-DbaLastBackup -SqlServer $server -Destination $TestServer -MaxMB 5
    $to = ''
    $smtp = 'smtp.gmail.com'
    $port = 587
    $cred = Get-Credential
    $from = '[email protected]'
    $subject = 'The Beard Reports on Backup Testing'
    $Body = $Results | Format-Table | Out-String
    Send-MailMessage -To $to -From $from -Body $Body -Subject $subject -SmtpServer $smtp -Priority High -UseSsl -Port $port -Credential $cred</pre>

07 -email

You can of course attach any of the above files as an attachment using the -attachment parameter in Send-MailMessage

Database

Of course, as good data professionals we probably want to put the data into a database where we can ensure that it is kept safe and secure

dbatools has a couple of commands to help with that too. We can use Out-DbaDataTable to create a datatable object and Write-DbaDatatable to write it to a database

Create a table

    USE [TestResults]
    GO
    CREATE TABLE [dbo].[backuptest](
    [SourceServer] [nvarchar](250) NULL,
    [TestServer] [nvarchar](250) NULL,
    [Database] [nvarchar](250) NULL,
    [FileExists] [nvarchar](10) NULL,
    [RestoreResult] [nvarchar](200) NULL,
    [DBCCResult] [nvarchar](200) NULL,
    [SizeMB] [int] NULL,
    [Backuptaken] [datetime] NULL,
    [BackupFiles] [nvarchar](300) NULL
    ) ON [PRIMARY]
    GO

then add the data

    Import-Module dbatools
    $TestServer = 'SQL2016N1'
    $Server = 'SQL2016N2'
    $servers = 'SQL2005Ser2003','SQL2012Ser08AG1','SQL2012Ser08AG2','SQL2012Ser08AG3','SQL2014Ser12R2','SQL2016N1','SQL2016N2','SQL2016N3'
    ## Run the test for each server and save to a variable (This uses PowerShell v4 or above code)
    $Results = $servers.ForEach{Test-DbaLastBackup -SqlServer $_ -Destination $TestServer -MaxMB 5}
    ## Convert to a daatatable.
    $DataTable = Out-DbaDataTable -InputObject $Results
    ## Write to the database
    Write-DbaDataTable -SqlServer $Server -Database TestResults -Schema dbo -Table backuptest -KeepNulls -InputObject $DataTable

and query it

08 - Database.PNG

Hopefully that has given you some ideas of how you can make use of this great command and also one of the benefits of PowerShell and the ability to use objects for different purposes

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 `Get-Module dbatools`

and update it using an Administrator PowerShell session with

 `Update-Module dbatools`

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

`Install-Module dbatools`

Then you can use

`Update-dbatools`

Comments