In this article, we will explore database backup validation by with SQL restore database operations using DBATools.

DBA should regularly conduct database restore drills for production database backups. We might face a situation where we need to restore the database from previous backups, but it fails because the backup file is corrupt. To avoid that type of situation, we should restore databases and perform consistency check using the DBCC CHECKDB command.

If you are managing a large production environment, it is challenging to conduct restoration drills for each database. You can write custom scripts to do this task, but again it will require expertise in writing t-SQL queries.

Test-DbaLastBackup function in DBATools

In my previous article, SQL Restore Database using DBATools, we explored the open source PowerShell module DBATools for database backup and restoration. We can perform disaster recovery drills using the Test-DbaLastBackup command of DBATools in PowerShell. In this article, we will explore Test-DbaLastBackup and its usage for SQL restore database drills.

In the following screenshot, you can see all commands related to backups in DBATools.

We can get more information about Test-DbaLastBackup using the Get-help command.

We can test the last set of full backups in a SQL instance using the Test-DbaLastBackup command.

It performs the following steps

• It gathers last full backup information for the specified database. If we do not specify a particular database, it gathers information for all databases
• It restores the last full backup of a database with a new name in specified server. If we do not specify any instance, it restores database SQL Server on source instance. By default, the new database name is in the format of dbatools-testrestore-$databaseName. We can specify a custom database name as well
• It also renames the logical and physical file of a new database. It ensures no conflict with database filenames
• It performs DBCC CHECKTABLE to Checks the integrity of all the pages and structures for all tables in a new database
• In the last step, it drops the newly restored database

Example 1: Execute Test-DbaLastBackup command for all databases in SQL instance

In this example, we want to perform a SQL restore database operation on the same instance. We need to specify the SQL instance name in parameter -SqlServer. In the following query, we will use Out-GridView format to get a user-friendly output.

• Note: We should have sufficient free space in the drive. I would recommend the SQL restore database operation on a different instance for validation purpose.

Once you execute this command, it shows database restoration stats, and you can see the percentage competition status as well.

While restoration is in progress, right-click on the databases and refresh it. You can see a new database in the format of dbatools-testrestore-$databaseName. This database will be in restoring mode. In the following screenshot, you can see database restoration is going on for WideWorldImporters database.

In the following grid view, we can see the following information

1. RestoreResult and DbccResult are successful for a few databases. DBATools reads databases full backup history. It further searches particular backup in the directory in which we took database backup
   
   

   For example, we took AdventureWorks017 database backup in C:\SqlShack folder and SQLShackDemo database backup in C:\Temp folder. We can see the last backup location in BackupFiles column. It restores database SQL Server on the specified instance and performs validation checks.

2. In my test instance, have not performed a backup for each database. I further removed database backup files that are not required for me. In this output, you can see false status in RestoreResult and Skipped status in DbccResut. It can skip database restoration due to the following tasks
   
   
   • No full backups for specified database
   • Backup files do not exist in a backup directory

Let us perform a full backup for the Master database and execute Test-DbaLastBackup again. In the following screenshot, we can it restores system databases backup as well.

It restores the master database as dbatools-testrestore-master but does not perform DBCC CHECKTABLE on this database.

We can check the error logs in SQL Server (Expand Management -> SQL Server Logs). In the error logs, you can find an entry for database restoration, recovery and consistency check as well.

Example 2: Restore a particular database and validate it using Test-DbaLastBackup

In the previous example, we did not specify any database name for restoration and validation check. Suppose we want to perform validation test for a particular database. We can specify database name using -Databases parameter. In the following query, we want to perform a SQL restore database operation for consistency check for AdventureWorks2017 database.

Execute the following command in PowerShell.

It starts restoration for particular database only.

In the output, you can see the status for only AdventureWorks2017 database.

Example 3: Do not drop the restored database after consistency check

As specified earlier, the Test-DbaLastBackup command drops the restored the database after performing a consistency check on it. Suppose, once the database consistency check is completed, we do not want to drop it. It should be in an online state and accessible to users.

We can specify a parameter -NoDrop to accomplish this task for us. Let us run Test-DbaLastBackup to perform a SQL restore database operation of AdventureWorks2017(last full backup) database, perform consistency on it and keep it online afterwards. Execute the following command with -NoDrop parameter.

Now, connect to a SQL instance, and we can see online database dbatools-testrestore-AdventureWorks2017.

Example 4: Specify a Custom name for the restored database using Test-DbaLastBackup

In previous examples, once we perform a SQL restore database operation using Test-DBAlastBackup, it gives database name in the format of dbatools-testrestore-$databaseName.

Suppose we do not want to use a default name for the restored database. In this example, I require to have a database in the format of DRDrill_$databasename. We can do this by specifying a name using -Prefix parameter.

In the following command, we specified prefix DRDrill along with parameter -Nodrop to keep a database in online status.

In the following screenshot, you can verify database name in the format of DRDrill_$databasename.

Example 4: Verify backups without database restoration

In SQL Server, we can verify using the RESTORE VERIFYONLY command to verify database backups. It performs internal validation of backup file and ensures it is not corrupt.

We can do this using a parameter -VerifyOnly in Test-DbaLastBackup command. It does not perform a SQL restore database operation and performs validations only.

We should have a unique name for a restored database. If a database already exists, it gives the following message.

WARNING: DRDrill-AdventureWorks2017 already exists on KASHISH\SQL2019CTP – skipping

Let us drop database DRDrill-AdventureWorks2017 and rerun the command.

In the following screenshot, you can see the status is Verify successful.

Example 5: identify corrupt database backup using Test-DbaLastBackup

For this example, I have corrupted the last full backup file of the Master database. I opened the file using Edit plus and written random characters in the backup file. Once changes are done, save and exist file.

Note: Please do not perform this step on production database backup files. It might damage the backup file permanently.

Let us run Test-DbaLastBackup command with -VerifyOnly parameter.

In the following screenshot, we can see that backup file verification failed. We cannot use this backup file for our SQL restore database operation.

Let us try to restore this backup file with the following command.

We get a detailed error message.

Example 6: Restore database files to a specified directory

By default, Test-DbaLastBackup restores the database in default directories. To verify the default path right click on SQL Instance and properties. In the properties page, go to Database Settings and verify database default locations.

Previously, we restored a SQL Server database without specifying database file locations. Once restoration is completed, verify the file path. You can see database files in default locations.

Suppose we do not want to create restored databases in a specified directory. We want to create data and log file in C:\TEMP\Backup folder. We can specify a directory using DataDirectory and LogDirectory parameter.

Verify file locations using the sp_helpfile command in a restored database. In the following screenshot, we can verify files exist in specified directories.

We can specify destination SQL instance name using -Destination parameter.

Example 7: Export output in a file

If we are have a large number of databases, we want to get a SQL restore database. We want further validation results in a file instead of getting it on screen. We can export the output of Test-DbaLastBackup in various formats using Out-file.

Export result in a Text format

It does not provide any output on the screen if we specify the output file.

Now, you can go to the path and open a text file in a notepad.

Export result in a CSV format

Execute the following command to get output in a CSV format.

Go to the path and open the CSV file to view the output.

Export result in an HTML file

Conclusion

In this article, we explored useful commands in PowerShell module DBATools to validate the last full backups by with a SQL restore database operation and performed a consistency check on it. We should regularly perform this kind of checks on backup files to safeguard you against backup corruption issues that might come to a later date. I suggest you explore these commands in your environment and be familiar with them.

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023
• Learn the PostgreSQL COALESCE command - January 19, 2023