As a database administrator (DBA), one of your primary responsibilities is to ensure the integrity and availability of your SQL Server databases. This includes regularly backing up your databases to protect against data loss. However, simply performing backups is not enough. You also need to verify the integrity of those backups to ensure that they can be successfully restored when needed.

In a scenario where a database backup contains corrupted data, you may not realize the issue until it’s too late. Corrupted data in infrequently accessed tables can go unnoticed for a long time and can cause significant problems when attempting to restore the backup. To avoid this situation, it is crucial to validate your database backups regularly.

Validating backups is not as straightforward as performing backups and restores. The native maintenance plan in SQL Server does not provide a built-in tool for validating backups. However, there is a solution that allows you to automate and schedule the validation process.

The following T-SQL script can be used to automate the validation of your database backups:

-- EXEC usp_VerifyDB @DBName = 'backuptestdb', @RemoteServer='.'
IF EXISTS (SELECT 1 FROM sys.procedures WHERE Name = 'usp_VerifyDB') DROP PROCEDURE usp_VerifyDB
GO
CREATE PROCEDURE usp_VerifyDB (@DBName AS VARCHAR(100), @RemoteServer AS VARCHAR(100) NULL) AS 
BEGIN
PRINT '-- STEP -1. CREATE A LINKED SERVER TO REMOTE/LOCAL SERVER --'
...

This script creates a stored procedure called usp_VerifyDB that takes the database name and an optional remote server name as parameters. It first checks if the backup and restore servers are the same. If they are different, it creates a linked server to the backup server from the restore server.

The script then checks if the specified database and backup exist. If they do, it retrieves the latest full backup file for the database. It creates a new database name by appending the current date to the original database name and restores the backup to the new database name.

After restoring the database, the script checks for any data corruption using the DBCC CHECKDB command. It captures the results and drops the cloned database.

Finally, the script stores the results of the DBCC CHECKDB command in a temporary table and displays the results.

By regularly executing this stored procedure for all your user databases, you can ensure that your backups are worth the restore time and that your data is not corrupted.

It is important to note that this script is just a starting point and can be customized to fit your specific backup and restore environment. You can schedule the execution of this stored procedure using SQL Server Agent or any other scheduling tool of your choice.

Remember, performing backups is only one part of the DBA job. Validating those backups is equally important to ensure the availability and integrity of your SQL Server databases.

For more information and the original source of this script, you can visit the blog post by Data Steve at https://datasteve.com/2019/09/11/verify-your-database-backups/.