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

CSV
Or maybe you need a CSV
Test-DbaLastBackup -SqlServer sql2016n2 -Destination SQL2016N1 -MaxMB 5 | Export-Csv C:\temp\Test-Restore.csv -NoTypeInformation

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

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

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!

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 = 'Beard@TheBeard.Local'
$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>

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

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
Pingback: Using Pester with dbatools Test-DbaLastBackup | SQL DBA with A Beard
Pingback: Test your Sqlserver backups on Linux with PowerShell and dbatools | SQL DBA with A Beard
Pingback: Test the SQL Server database collation with PowerShell and dbatools | SQL DBA with A Beard
Pingback: Getting SQLServers Last Known Good DBCC Checkdb with PowerShell and dbatools | SQL DBA with A Beard
Very nice to show what exports can be used and how the function can be utilized even more. I have a similar script that uses another kind of export to get the data to excel if you’re interested: https://github.com/sanderstad/Test-MyBackup
Pingback: Testing the Identity Column usage in SQL Server with PowerShell and dbatools | SQL DBA with A Beard
I just went through all your examples. I am intrigued by the Test-DbaLastBackup. While we have a TSQL based solution that does this. I was thinking of switching it over. But I had a couple questions. How would you go about pointing to an existing Full/Diff/Log instead of taking a backup then restoring it? Some of our databases are large and a backup at runtime would not be feasible. Also, this process I would guess be better fit around your backup routines to make sure things are restoreable. Just looking for some guidance on expanding on this solution.
Test-DbaLastBackup does use the existing backups from the latest backups taken using the data in msdb table https://dbatools.io/functions/test-dbalastbackup/
I woulda recommend add you said automating this to run after your Full or Diff backups using Agent Jobs or scheduled task or azure automation
So it only needs the source instance to read msdb? Could it be pointed at a folder structure for the backups?
To my knowledge, not at present. Sounds like a good thing to raise an issue in the github repository
Pingback: dbachecks – Save the results to a database for historical reporting | SQL DBA with A Beard
It appears the output from the Test-DbaLastBackup command has changed, so the current table structure is missing the needed fields. Can this page be updated to reflect this, or how to get the proper field datatypes?
Ignore my last post, I was a little quick to respond and should have dug deeper. The Out-DbaDataTable command is replaced with ConvertTo-DbaDataTable and the Write-DbaDataTable now has -AutoCreateTable. Works like a charm.