Rajendra Gupta
output of Test-DbaLastBackup command

Validate backups with SQL restore database operations using DBATools

April 8, 2019 by

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.

Test-DbaLastBackup function in DBATools

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

Test-DbaLastBackup function in DBATools help page

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.

Execute Test-DbaLastBackup command for all databases in SQL instance

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.

New database created by  Test-DbaLastBackup command

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

output of  Test-DbaLastBackup command

  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.

Examples of Test-DbaLastBackup command

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

Examples of Test-DbaLastBackup command

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.

SQL Server error logs

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.

Restore database SQL Server - Examples of Test-DbaLastBackup command for a single database

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

Output of Test-DbaLastBackup command

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.


Examples of Test-DbaLastBackup command with NoDrop

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

Examples of Test-DbaLastBackup command with NoDrop

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.


Restore database SQL Server - Examples of Test-DbaLastBackup command with Prefix

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

Examples of Test-DbaLastBackup command with Prefix

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

Examples of Test-DbaLastBackup command

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

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

Examples of Test-DbaLastBackup command with VerifyOnly

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.

Examples of Test-DbaLastBackup command for backup verifiation failure

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

We get a detailed error message.

Restore database SQL Server - Examples of Test-DbaLastBackup command for backup verifiation failure

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.

SQL Server properties

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.

Output of sp_helpfile

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.


Examples of Test-DbaLastBackup command to restore on a specific directory

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

Examples of Test-DbaLastBackup command to restore on a specific directory

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.

Export output of Test-DbaLastBackup commandin a text 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 output of Test-DbaLastBackup commandin a CSV file

Export result in an HTML file


Export output of Test-DbaLastBackup commandin a 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

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

1,002 Views