Using PowerShell for SQL Backup Verification

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:

You can download the script from my Github Site .

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  
 }