Earlier this year, we had a non-critical SQL server to crash. Come to find out, the backups were successful every night, but the data in the backup was corrupt. Needless to say, the server had to be recreated from scratch. Thankfully it was a non-critical server.
We wanted a way to automatically verify SQL backups are valid. Using PowerShell, I wrote the following script that runs RESTORE VERIFYONLY against the latest backup contained in the designated directory. The script looks for the output to be "The backup set on file 1 is valid". If it does not return that, then the script will exit with an error code 1. I am using this script in Microsoft Orchestrator, so the link looks for an error code 1 to proceed to the next task of sending an email to the pertinent IT staff if the backup is not valid. If it is successful, an error code 0 is generated, and the link in Orchestrator does not proceed. The script can be used in Orchestrator or Azure Automation. It can also be modified using the Send-MailMessage cmdlet to send an email out upon failure. This can be added to be used in a scheduled task if the other two options are not available.
To use this script, you will need to define the following parameters:
- $SQLBackupDir designates the location where the .BAK files are located
- $TimeSpan defines how many days out a backup can be invalid or missing before an email is triggered
- $SQLServer is the name of the SQL server
- $SQLDatabase is the name of the SQL database
- $ModuleName is the name of the PowerShell SQL module, which should be SQLServer
While talking about the PowerShell module, I have written this script to automatically install it if it does not exist. The script will exit with an error code 2 if it is unable to install the module, so further troubleshooting can be performed.
The script goes out an retrieve the latest backup from $SQLBackupDir. We keep 5 backups of the SCCM server on hand, for instance, so the script gets the most recent of those five.
As you can see in the screenshot below, the script successfully executed against the SCCM SQL server and verified the backup is good.
Below shows an easy implementation of this script in Orchestrator:
As you can see in the screenshot below, the script successfully executed against the SCCM SQL server and verified the backup is good.
Below shows an easy implementation of this script in Orchestrator:
NOTE: There may be differences in your environment, such that you might need to change the actual SQL query to work.
<#
.SYNOPSIS
SQL Backup Verification
.DESCRIPTION
This script retrieves the latest SQL database backup file and verifies its date matches the current date while also verifying the backup is good.
.PARAMETER SQLBackupDir
Location where the SQL backups exist
.PARAMETER TimeSpan
Number of days allowed since last SQL backup
.PARAMETER SQLServer
Name of the SQL server database to verify the backup
.PARAMETER SQLDatabase
Name of the SCCM SQL database the backup file is for
.PARAMETER ModuleName
Name of module to import
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 11/11/2019 4:49 PM
Created by: Mick Pletcher
Filename: SQLBackupVerification.ps1
===========================================================================
#>
[CmdletBinding()]
param
(
[ValidateNotNullOrEmpty()]
[string]$SQLBackupDir,
[ValidateNotNullOrEmpty()]
$TimeSpan,
[ValidateNotNullOrEmpty()]
[string]$SQLServer,
[ValidateNotNullOrEmpty()]
[string]$SQLDatabase,
[ValidateNotNullOrEmpty()]
[string]$ModuleName
)
#Import SQL Server PowerShell Module
If ((Get-Module -Name ((Import-Module -Name $ModuleName -ErrorAction SilentlyContinue -Force -PassThru).Name)) -eq $null) {
#Install module if it does not exist
Install-Module -Name $ModuleName -Confirm:$false -Force
#Verify module got installed. Exit the script if it failed
If ((Get-Module -Name ((Import-Module -Name $ModuleName -ErrorAction SilentlyContinue -Force -PassThru).Name)) -eq $null) {
Write-Host 'Failed'
Exit 2
}
}
#Retrieve file attributes from the latest SQL backup
$LatestBackup = Get-ChildItem -Path $SQLBackupDir -Filter *.bak -ErrorAction SilentlyContinue | Select-Object -Last 1
#Verify there is a backup file that exists
If ($LatestBackup -ne $null) {
#Check if the latest SQL backup is within the designated allowable timespan
If ((New-TimeSpan -Start $LatestBackup.LastWriteTime -End (Get-Date)).Days -le $TimeSpan) {
#Execute SQL query to verify the backup is valid
$Verbose = $($Verbose = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ('RESTORE VERIFYONLY FROM DISK = N' + [char]39 + $LatestBackup.FullName + [char]39) -QueryTimeout 0 -Verbose) 4>&1
If ($Verbose -like '*The backup set on file 1 is valid*') {
Write-Output 'Success'
Exit 0
} else {
Write-Output 'Invalid Backup'
Exit 1
}
} else {
#SQL Server did not perform a backup within the designated timespan
Write-Output 'Latest Backup does not exist'
Exit 1
}
} else {
#Backups are not being performed
Write-Output 'No backups available'
Exit 1
}
