Reading Time: 3 minutes
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 |
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”