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' #>