Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 343

Coniguring a manageable database
consistency and integrity process for
multiple databases and a VLDB
environment
The data quality must be kept intact and the method to keep up is difficult to maintain for
multiple databases and a VLDB environment too. The default response to recover the data
from a disaster is to restore it from the backup tape of a file, but in cases where the source
media such as a tape is crinkled or the backup file server disk has a problem, the recovery
can be expensive. In this case, gigabytes of data might be lost just because of a hardware
problem. There are few methods available in SQL Server to recover the data using the database
consistency process, which involves a repair of corrupted data stored within the database.
As mentioned earlier, the data recovery will involve a REPAIR option that must be used as a
last resort. Using this option might present a potential risk of losing database pages that will
cause further data loss. However, if a nonclustered index is reported as corrupted then the
ideal action is to drop and recreate the same index.
In this recipe, we will go through the strategic steps in configuring a manageable database
consistency and integrity process for multiple databases and a VLDB environment. The recipe
will cover the best practices in performing database consistency and integrity check steps to
identify any internal errors to resolve in a proactive manner.
Getting ready…
There are no specific prerequisites to install or modify except the instance must be a SQL
Server 2008 version or higher, having at least one user database to perform the steps defined
in the recipe.
How to do it...
The recipe includes a multiple step approach by reviewing the important commands to
validate and check for issues within the database. The steps involve checking database page
usage and verifying allocation errors, and also include the integrity of database and database
objects with a final touch-up to validate the integrity at the table and data validation level.
The following steps are required in configuring a manageable database consistency and
integrity process for multiple databases and a VLDB environment.