How to Automatically Validate SQL Server Backups With Test-DbaLastBackup (Dbatools)

In this blog post, we will audit the dbatools command Test-DbaLastBackup. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Test-DbaLastBackup is a powerful tool to automate the validation of a backup. Backup testing should be a regular part of SQL Server maintenance and this command works well in automating this task.

Scope

Dbatools is a community project full of bright minds. This audit series is more an informational series on useful capabilities and considerations rather than rigorous testing. This is to inspire even greater use of PowerShell and by extension dbatools as I believe it will assist in automating many tasks as a DBA.

SQL Server Testing Environment Setup

I created a multi-subnet lab with three developer edition SQL Server instances, one SQL Express and a network share based on this blog post by Jonathan Kehayias.

Step-by-Step Execution of Test-DbaLastBackup

1. Download and Install

You are probably familiar with dbatools at this point, but if not now is the perfect time to familiarize yourself with the install and use of the module. On your test environment you can open PowerShell and run the following scripts. These will install Dbatools and add it to the path for future projects as well as import it into the current session for the purpose of testing the scripts of this blog post.

Install-Module Dbatools
Import-Module Dbatools

2. Initial Setup

To save both me and you time, I will skip the test environment steps since they are identical here to my first post.  Instead, I will gripe about how it seems so few really appreciate the need for backups to be regularly tested for restorability and integrity. Many subscribe to the idea of a backup strategy and assume every backup will be fine if needed. This type of thinking leads to the unfortunate circumstance of finding out that there are backups that have some sort of issue in the middle of an emergency.

Perhaps you are one of the seemingly few who appreciate the importance of such testing, AND are taking steps to implement a backup testing strategy. If that is you, and if not, hopefully it is you by the end of this post, then Test-DbaLastBackup is a very simple tool for getting started with a basic full backup testing strategy for each week. To harp on my previous point, you are only as good as your last good restorable and usable backup. Having this process running after each full backup and having notifications from the results should be added to every DBA’s toolbox.

3. Execution

Now that we have the environment set up and I have fully convinced you that testing the restorability and integrity of your backups is worth your time, we can begin showing the capabilities of Test-DbaLastBackup in a couple of scenarios.

Scenario 1: A One-off SQL Server Backup Verification

In this first scenario, we’ll restore a copy of all databases on an instance to the same instance and run DBCC CheckDB on all of them.

If your instance is not that busy and is overprovisioned on space and resources like my test environment is, then you can possibly handle restoring a copy of all of your databases onto the same instance and running DBCC CheckDB on those copies. This script is so short it won’t even need splatting to look nice on the web. I added the Out-GridView since it is nicer to navigate the results than scrolling up and down the terminal output.  This is a simple scenario I used to test the functionality, output, and convenience of the command and it works like a charm!

Test-DbaLastBackup -SqlInstance LabSQL1 | Out-GridView

Scenario 2: Regularly Scheduled SQL Server Backup Verification

This is the ideal scenario for this command. Having a secondary job, or perhaps a second JobStep to run after backups complete to start the verification process on the same or a separate instance. I will be designing this solution as a separate job that has no schedule, while adding a second jobstep to my full backup job to start the verification process. That will look like this when complete:

/* This is the code to add the second jobstep that begins the verification job to a standard Ola Maintenance User-Databases Full backup job. This script also has the changes needed for the second step to work to be accessible from the first step. */
USE [msdb]
GO
/****** Object:  Step [DatabaseBackup - USER_DATABASES - FULL]    ******/
EXEC msdb.dbo.sp_delete_jobstep @job_name=N'DatabaseBackup - USER_DATABASES - FULL', @step_id=1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DatabaseBackup - USER_DATABASES - FULL', @step_name=N'DatabaseBackup - USER_DATABASES - FULL', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXECUTE [dbo].[DatabaseBackup]
@Databases = ''USER_DATABASES'',
@Directory = N''\\LabShare\SQLBackups'',
@BackupType = ''FULL'',
@Verify = ''Y'',
@CleanupTime = 336,
@Checksum = ''Y'',
@LogToTable = ''Y''', 
		@database_name=N'DB_Admin', 
		@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt', 
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DatabaseBackup - USER_DATABASES - FULL', @step_name=N'Start verification job', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC msdb..sp_start_job @job_name = ''DatabaseBackup - FULL Backup Verification''', 
		@database_name=N'master', 
		@flags=0
GO

A simple configuration, mostly to separate out the backup and verification history, as well as to make sure the logging is a bit cleaner. May be more cumbersome in practice but your milage may vary.

This next script is the job creation command for the verification job.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DatabaseBackup - FULL Backup Verification', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@category_name=N'[Server Maintenance]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DatabaseBackup - FULL Backup Verification', @server_name = N'LABSQL1'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DatabaseBackup - FULL Backup Verification', @step_name=N'Run PowerShell Script', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'Powershell.exe -executionpolicy Bypass -file "D:\SQLRoot\MSSQL16.MSSQLSERVER\MSSQL\JOBS\Automated Backup verification.ps1"', 
		@database_name=N'master', 
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - FULL Backup Verification', 
		@enabled=1, 
		@start_step_id=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'', 
		@category_name=N'[Server Maintenance]', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'', 
		@notify_page_operator_name=N''
GO

Finally, the script that is the star of the show. I have made a few changes that align closer to industry best practices, primarily that this verification process is best offloaded to a non-production SQL Server instance to make sure production workloads are not impacted. Splatting is back to make sure it is nice to look at. We have also added try/catch logic and the exit code that will cause the process to stop and the agent job to report as failed if there are any errors or failed DBCC CheckDBs found.

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -register

$Splat = @{
    #SqlCredential              = $credential
    #DestinationSqlCredential   = $Destcredential
    SqlInstance                 = 'LabSQL1'
    Destination                 = 'LabSQL2'
    }   

$Errors =@()

Try {
    $results = Test-DbaLastBackup @splat 
    
        Foreach ($item in $results) {
            IF ($item.Database -eq 'master'){Write-Host '[Master is skipped]'
            } ELSE {
                Write-Host " - [$($item.Database) = $($item.DBCCResult)]"
                If ($item.RestoreResult -ne 'Success') 
                    {$errors += "- [Restore for $($item.Database) failed.]"
                    Throw}
                If ($item.DBCCResult -ne 'Success' ) 
                    {$errors += "- [DBCC CheckDB for $($item.Database) failed.]"
                    Throw}
           }
        }
    } 
 Catch {
        Write-Error $_
        $Errors | Foreach-object { Write-host $_}
        [System.Environment]::Exit(1)
}

There’s a bit going on here, let’s go through it together. The trust certificate command is on line 1 followed by the splatting block until line 8. After that we create an array to catch the errros on line 10, and then proceed into the try/catch block starting on line 12. The try runs the Test-DbaLastBackup script and stores all of the output into the $Results variable. We cycle through each of those returned objects and on line 16  we add the step to skip the master output due to master being unrestorable on different instances. Lines 17-27 end the block by returning all of the results, as well as adding errors and triggering a throw if any the restores or checkdbs failed. Lastly, 28-32 writes any errors back as well as exits the process with error code 1, meaning it will cause the agent job to return a failed result, increasing visibility for the results.

Test-DbaLastBackup Results

Scenario 1

Restore a copy of all databases on an instance to the same instance and run DBCC CheckDB on all of them.

This is a simple exploratory example. It runs quick and seamlessly, and with out-gridview it is easier to see what happened, and where you may want to tweak the output or identify results to collect. I removed some columns to get everything into the width of my screenshot and also included which columns were removed:

Simple and to the point in my opinion. The next scenario is where things get a bit more impressive.

Scenario 2: Regularly Scheduled SQL Server Backup Verification

The second scenario offers a complete solution for a general SQL Server instance, meaning not particularly huge databases. However, if you have such an environment, you likely already have some other method of checking your data for integrity. The main benefit of the second job that calls the process is the history and notification possibilities. You likely already have some sort of process to notify you of job failures, with the setup shared the verification job will fail if either the restore or checkdb fail and neither should be failing unless you are out of space on the restore host, or there is some integrity issue with your restored database, which was our goal to begin with.

The pictures below show that the backup job now has a secondary step to kick off the verification job, and the second screenshot shows the history of the verification job with both sets of results (I had to pretend with my failure result, typically it will not show success and then failed, it will show failed and failed in the error string!)

Test-DbaLastBackup Review and Analysis

There is a common theme with the commands I am choosing. Specifically, I believe them to be extremely useful to the DBA and that they should be added to your PowerShell DBA toolbox. Test-DbaLastBackup is another one of those specific commands that opens up the world of automation by simplifying a necessary but woefully under-addressed need in the DBA world.

The capabilities of Test-DbaLastBackup to simplify such an important task hopefully encourages you to begin the process of implementing this testing in your own environment. Again, this is all to avoid finding out in the midst of an emergency that your backup has corruption or your network share storage is failing, or too slow to meet your RTO.

Warnings and Caveats

The warning here will spread across all of the commands we audit as well as anytime you use PowerShell. This script restores a database and runs checkdb on it. For the most part, the restore and checkdb process are self-contained. The only consideration here is that if the script is improperly configured and is pointed at a production instance or an instance without enough resources, it can cause a strain on your production workloads or cause the testing process to fail or only partially complete. Take care that you identify a safe, ideally non-production instance to use for this process to keep things running smoothly.

Recommendations

It is best practice to ensure that your SQL Server backups can be restored and have no integrity issues introduced by the backup and restore process. That task is handled easily by using scenario 2’s solution because it checks every full backup as it is taken every week. You may want to run this at a different time, ensuring you get diff and log backups in the process, so perhaps running it mid-week if you follow the weekly full schedule. No matter what you decide, our recommendation is that your backups are tested.  

Conclusion

Test-DbaLastBackup is a very useful tool, especially since ensuring your backups are verified restorable and without integrity issues. This should be a part of business continuity planning and should be done with every mission-critical database. Testing backups and running integrity checks manually would be a very time consuming process through the GUI. Using Test-DbaLastBackup will simplify the process and be your time and data-saving hero!