Validating database integrity on a regular basis is a fundamental task that is often forgotten by the accidental DBA. Fortunately SQL Server database corruption issues aren’t very common, however 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 gathering and storing this information, I just prefer to generate report and keep the logic outside of the database for smaller environments.
In addition, the following article might be of interest: Why PowerShell for SQL Server?
First things first
Before reading on, it’s important to know that DBCC CHECKDB is a CPU- and disk-intensive operation and therefore 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.
You’ll need the Server Management Objects library which can be installed separately (Just 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.
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. This message can be ignored. 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’m using integrated authentication therefore the script needs to be executed using a valid user context.
In this part you could alter the behavior and validate only a single database or a custom list (hard coded 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 exception has been thrown. In this case the script will log the execution details and moves to the next database.