How to Test SQL Server Backups Using dbatools

The call comes in. “Something’s happened. We need to restore the production database. Can you do it? And how long will it take?” No DBA wants to receive that call but, frankly, answering those calls and the ensuing questions are part of a DBA’s job. And there’s no better way to know that you can, and how long it will take, than to periodically test your SQL Server backups.

In How Often Should I Test My SQL Server Backups?, I shared a schedule we recommend to our customers for monitoring, verifying, and testing their SQL Server backups. That last part, testing the backups, is important, really important. So, I shared a couple of basic T-SQL scripts for restoring a database to a test location and running DBCC CHECKDB on it.

Fans of PowerShell know that testing SQL backups can also be done using dbatools. Let’s have a look. For this exercise, you’ll need PowerShell, of course, along with a recent copy of dbatools. You can download dbatools for free.

Backing up a SQL Server database with dbatools

Let’s start by backing our BaseballData database using the dbatools Backup-DbaDatabase command. This creates a native SQL Server backup file, the same file that is created with the T-SQL BACKUP DATABASE command.

In this example, I’m creating a Full back up the BaseballData database that resides on the local default instance of SQL Server. The instance is running in a Linux container, so the -Path parameter is formatted for a Linux file system.

Backup-DbaDatabase -SqlInstance localhost -SqlCredential sa -Database BaseballData -Type Full -Path /var/opt/mssql/backup

The command output shows that a SQL backup file has been created.

Testing a SQL Server backup using dbatools

Now that we have a SQL backup file, let’s use the Test-DbaLastBackup command to test the backup. This command performs several steps for us, including:

1. Restoring the SQL backup file to a destination with a new name. The destination can be the same or a different SQL Server instance.
2. Changing the database name to prevent confusion. The restored database will be called “dbatools-testrestore-BaseballData” by default. I can provide a different name, if I wish.
3. Changing the SQL database file names to prevent conflicts with original database files.
4. Checking the newly restored database with DBCC CHECKDB to confirm that it is in good working order.
5. Dropping the newly restored database as part of the clean up.

Notice that I didn’t specify which SQL backup file to test. The command finds the most recent Full backup to test.

Test-DbaLastBackup -SqlInstance localhost -SqlCredential sa -Database BaseballData   

The result of theTest-DbaLastBackup command are sent to the output screen by default. Of course, we can also send the results to a file.

Looking at the output, we can see that the database was successfully restored, and how long it took to restore it. We can also see that DBCC CHECKDB ran and how long it took to complete. We can dive into the results of the integrity check, too. See When Was the Last Known Good DBCC CHECKDB Integrity Check? for more information.

Test your SQL Server backups often

Using PowerShell and the Test-DbaLastBackup command makes testing your SQL backups relatively easy. Yes, you still need the disk space and server resources to do it. But being able to answer that frantic question “Do we have a good backup?” makes it definitely worth while.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us.