Free Tool: SQL Server RestoreChecker Utility

I've been delivering a presentation at SQL Saturday and PASS events for many years called "Top 10 DBA Mistakes." The number one mistake that I've personally known a DBA to be fired for was not being able to restore a mission-critical database after a crash. The CIO was furious that the DBA had been creating backups for years, but never once checked them to see if they were recoverable. The DBA's backups weren't recoverable, as you can probably guess.

Related: 8 Free SQL Server Tools

A handy little utility called RestoreChecker automatically runs in the background and performs several tests to verify that your backup files are restorable and data integrity is ensured. The utility also pushes your integrity checks off of the production server, possibly alleviating the intensive I/O load that's generated by various DBCC commands that are needed for proper maintenance.

Features

RestoreChecker is a simple command-line utility that automates the process of testing a backup that's taken from a source server and restoring it onto a target server. As RestoreChecker performs its restoration operations, it also checks data integrity for the database. In keeping with its simplicity, the utility returns a 0 or 1 depending on successful completion or failure, respectively.

The utility's behavior is controlled by setting various flags in a configuration file located at the working directory, which is usually located at C:\Program Files (x86)\RestoreChecker\. The configuration file contains the information required to operate the program. At least one row is required in the configuration file for the utility to work properly. The utility also creates a table, RestoreCheckerServers, detailing all of the databases that need to be restored. Refer to the vendor documentation for a full description of RestoreChecker's configuration file options.

RestoreChecker can also perform several different behaviors to ensure the recoverability of SQL Server backups, including the following:

• perform data integrity checks with DBCC CHECCATALOG, DBCC CHECKALLOCK, and DBCC CHECKTABLE operations
• dynamically restore the most recent and full database backup to another instance of SQL Server, either locally or over the network. Available operations include: the PHYSICAL_ONLY operation to minimize duration and I/O; using the TABLOCK operation to minimize duration; and restore using the WITH CHECKSUM operation if the backup file contains checksums

• drop a restored database to free up space on the target server
• ensure that only unprocessed backup files are handled to avoid duplicated effort
• ability to log any operation to a table for future tracking

RestoreChecker also logs all of its activity to the table RestoreCheckerLog to provide an easy and centralized place for follow-up. The creator of RestoreChecker, Yaniv Etrogi, has also provided several queries against this table so you can automate reports for failures and successes as needed.

System Requirements

RestoreChecker runs on OSs supported by SQL Server 2005 and later. Additional requirements include:

• .NET Framework 3.5 must be installed on the machine where the utility executes.
• The Windows account executing RestoreChecker.exe requires administrative privileges.
• The SQL Server account executing RestoreChecker.exe requires read-only permissions to MSDB..BackupSet and MSDB..BackupMediaFamily on the server where it executes.
   

Visit the SQL Server Utilities website to download the free version of RestoreChecker. As always, SQL Server Pro wants to hear your feedback at the Tool Time discussion forum.