Last week I showed how you can test your backups using the Test-DbaLastBackup function in the dbatools module.
The script makes it easier to iterate through multiple servers, export the data and to send the results to you using e-mail.
My good friend Rob Sewel wrote a nice post to take the function Test-DbaLastBackup a little further. In his example he uses COM objects to create the Excel document. I personally don’t like COM objects because they’re slow and you need the application installed on the running machine. Although COM objects have these disadvantages, they’re pretty solid en work smoothly most of the time.
I wasn’t satisfied with my script exporting to CSV because I would open the CSV file with Excel, so why not export it to an Excel file right away. I usually use the ImportExcel module to export data to Excel.
There was also a need to add the data and log directory to the command to make sure you would be able to use other drives to temporary save the database files.
To run the script you need the dbatools and ImportExcel module.
The last thing that was done is that this function is now available on Git and available for anyone to download via this link.
The function also can be seen below:
function Test-MyBackup
{
<#
.SYNOPSIS
Function to automatically test backups for SQL Server
.DESCRIPTION
The function is a wrapper around the function Test-DbaLastBackup function
from the dbatools module. It makes it easier to collect the information
and send the results.
.PARAMETER SqlServerSource
The instance where the backups should be tested for
.PARAMETER SqlServerDestination
The instance where the backups should be tested on
.PARAMETER DataDirectory
Directory used to place the data files for the database
.PARAMETER LogDirectory
Directory used to place the tranaction log files for the database
.PARAMETER OutputPath
Path where the results and log file should be saved
.PARAMETER Databases
Filter for the databases. This can be more than one
.PARAMETER SMTP
E-mail server to use to send the e-mail
.PARAMETER From
E-mail address from
.PARAMETER To
List of people that need to receive the e-mail
.PARAMETER MailCredential
Credential for the e-mail server if a credential is needed
.EXAMPLE
Test-MyBackup -SqlServerSource 'SQL1' -SqlServerDestination 'SQL2' -OutputPath 'C:\BackupTest\Log' -SMTP 'mail.corp1.com' -From 'backuptest@corp1.com' -To 'user1@corp1.com'
.INPUTS
.OUTPUTS
.NOTES
.LINK
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true, ValueFromPipeline=$false)]
[Alias("ServerInstance", "SqlInstance")]
[string[]]$SqlServerSource,
[Parameter(Mandatory=$true)]
[string]$SqlServerDestination,
[Parameter(Mandatory=$true)]
[string]$DataDirectory,
[Parameter(Mandatory=$true)]
[string]$LogDirectory,
[Parameter(Mandatory=$true)]
[string]$OutputPath,
[Parameter(Mandatory=$false)]
[string[]]$Databases,
[Parameter(Mandatory=$false)]
[string]$SMTP = $null,
[Parameter(Mandatory=$false)]
[string]$From = $null,
[Parameter(Mandatory=$false)]
[string]$To = $null,
[Parameter(Mandatory=$false)]
[PSCredential]$MailCredential
)
BEGIN
{
# Test if the outputpath is available
if(!(Test-Path $OutputPath))
{
try
{
New-Item -ItemType Directory $OutputPath
}
catch
{
Write-Error $_.ErrorMessage
}
}
# Clean up the output path variable
if(!($OutputPath.EndsWith("\")))
{
$OutputPath += "\"
}
# Create a timestamp
$TimeStamp = Get-Date -format 'yyyyMMddHHmmss'
# Set up the error log
$ErrorLog = "$($OutputPath)BackupTest_$($TimeStamp).log"
}
PROCESS
{
foreach($s in $SqlServerSource)
{
if ($s)
{
# Set the destination
if($SqlServerDestination -eq $null)
{
$SqlServerDestination = $s
}
Write-Host "Start testing backups for $($s)"
# Check the instance if it is a named instance
$ServerName, $InstanceName = $s.Split("\")
if($InstanceName -eq $null)
{
$InstanceName = 'MSSQLSERVER'
}
# Execute the test
try {
# Set up the output file
$OutputFile = "$($OutputPath)BackupTest_$($ServerName)_$($InstanceName)_$($timestamp).xlsx"
$OutputFileCsv = "$($OutputPath)BackupTest_$($ServerName)_$($InstanceName)_$($timestamp).csv"
# Test it!
try
{
if($databases -eq $null)
{
$results += Test-DbaLastBackup -SqlServer $s -Destination $SqlServerDestination -DataDirectory $DataDirectory -LogDirectory $LogDirectory
}
else
{
$results += Test-DbaLastBackup -SqlServer $s -Destination $SqlServerDestination -Databases $Databases -DataDirectory $DataDirectory -LogDirectory $LogDirectory
}
}
catch
{
# Oh oh error
$ErrorMessage = $_.Exception.Message
$TS = Get-Date -format 'yyyyMMddHHmmss'
"$($TS): $ErrorMessage" | Out-File $ErrorLog -Append
}
# Export the results
if($results -ne $null)
{
$results | Export-Excel $OutputFile -Show -AutoSize -AutoFilter -FreezeTopRow -ConditionalText $(
New-ConditionalText Skipped -BackgroundColor Gray -ConditionalTextColor Black
New-ConditionalText Success -BackgroundColor Green -ConditionalTextColor Black
New-ConditionalText True -BackgroundColor Green -ConditionalTextColor Black
New-ConditionalText False -BackgroundColor Red -ConditionalTextColor Black
New-ConditionalText Failed -BackgroundColor Red -ConditionalTextColor Black
New-ConditionalText -ConditionalType Last7Days -BackgroundColor LawnGreen -ConditionalTextColor Black
)
#$result | Export-Csv $OutputFileCsv -NoTypeInformation
}
# Check if the results need to be e-mailed
if(($SMTP.Length -ge 1) -and ($From.Length -ge 1) -and ($To.Length -ge 1))
{
try
{
Write-Host "Sending e-mail"
Send-MailMessage -From $From -To $To -Subject "Backup Test '$($ServerName)\$($InstanceName)' Completed" -Body "Backup Test for server $($ServerName)\$($InstanceName)" -Attachments $OutputFile -Priority High -SmtpServer $SMTP #-Credential $MailCredential
}
catch
{
# Oh oh error
$ErrorMessage = $_.Exception.Message
$TS = Get-Date -format 'yyyyMMddHHmmss'
"$($TS): $ErrorMessage" | Out-File $ErrorLog -Append
}
}
else
{
"Couldn't send email for backup test on instance $($ServerName)\$($InstanceName). Missing values in the e-mail parameters." | Out-File $ErrorLog -Append
}
}
catch {
# Oh oh error
$ErrorMessage = $_.Exception.Message
$TS = Get-Date -format 'yyyyMMddHHmmss'
"$($TS): $ErrorMessage" | Out-File $ErrorLog -Append
}
}
else
{
Write-Error "No instance given."
}
$results = $null
}
}
END
{
Write-Host "Done Testing."
}
}
Clear-Host
## Load module and run functions now..
Import-Module dbatools
Import-Module ImportExcel
<#Test-MyBackup `
-SqlServerSource 'SQL1' `
-SqlServerDestination 'SQL2' `
-DataDirectory "C:\BackupTest\Temp\Data" `
-LogDirectory "C:\BackupTest\Temp\Log" `
-OutputPath 'C:\BackupTest\Log' `
-SMTP 'mail.corp.com' `
-From 'backuptest@corp.com' `
-To 'user1@corp.com' #>
One thought on “Testing of backups updated”