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”