SQL Database Integrity Reports

Validating database integrity regularly is a fundamental task that is often forgotten by the accidental DBA. Fortunately, SQL Server database corruption issues aren’t common. Finding out that you have been backing up a corrupt database for weeks might be somewhat hard to explain to the business.

SQL Database Integrity Reports

The solution below demonstrates how simple it is to gather the information using PowerShell and the SMO client library. Of course, there are many ways of collecting and storing this information, and I prefer to generate a report. I also prefer keeping this logic outside of the database for smaller environments.

Also, the following article might be of interest: Why PowerShell for SQL Server?

First things first

Before reading on, it’s essential to know that DBCC CHECKDB is a CPU- and disk-intensive operation and, therefore, it’s better to perform on a non-production server. This will not only allow you to validate the integrity of the database without affecting production, but also test your restore strategy.

Prerequisites

You’ll need the Server Management Objects library, which you install separately (select the Client Tools SDK). SMO is a client-side library and therefore doesn’t require installing anything on the server for this to work.

The script.

First, it’s required to load the SMO libraries (PowerShell v3).

Note: Complete script can be found at the end of the page.

You might see and error regarding conflicts / unapproved verbs. Ignore this message. If you use the DisableNameChecking switch as below, you won’t see the message.

The first part will create an instance of the SMO Server object providing the SQL server instance name and custom timeout settings.

Note that I use integrated authentication; therefore, you will need to execute this script using a valid user context.

In this part, you could alter the behavior and validate only a single database or a custom list (hardcoded or perhaps based on a SQL statement)

And the portion below is responsible for simulating the DBCC CHECKDB command, which is not exposed within the SMO API. The script keeps track of the last executed statement and continues executing the following statement unless an exception has been thrown. In this case, the script will log the execution details and moves to the next database.

Complete script