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.
>-SqlServer Kashish\SQL2019CTP | Out-GridView
- 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
- 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.
- 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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017
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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -NoDrop
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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -NoDrop -Prefix DRDrill
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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -Prefix DRDrill -Verifyonly
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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases Master -Prefix DRDrill -VerifyOnly
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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases Master -Prefix DRDrill -NoDrop
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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -Prefix DRDrill -NoDrop -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup
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
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -Prefix DRDrill -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup | <strong>Out-file c:\temp\results.txt</strong>
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.
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -Prefix DRDrill -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup | Export-CSV c:\temp\results.csv -NoTypeInformation
Go to the path and open the CSV file to view the output.
Export result in an HTML file
>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -Prefix DRDrill -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup | ConvertTo-Html | Out-File c:\temp\results.html
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
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 firstname.lastname@example.org
View all posts by Rajendra Gupta