Difference between revisions of "Checking Database Integrity"

Latest revision as of 17:01, 28 June 2012

How to check database integrity

MSSQL

How to check the integrity of an MSSQL database

NOTE: All commands shown below are run as SQL queries. This can be done either through SQL Server Management Studio or through the SQL Tools in your control panel (if available for your database).

DBCC CHECKDB - Checking

The DBCC CHECKDB command allows you to check each table in your database for any corruption or inconsistency. It can also repair errors if they are found. However, it is always recommended to restore a database rather than repair it, if possible.

Example:

DBCC CHECKDB ([DB_NAME])

The above command will give you a lot of informational messages about each table checked. However, if you just want to see any errors, you can run it as follows:

DBCC CHECKDB ([DB_NAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DBCC CHECKDB - Repairing

If you run DBCC CHECKDB on your database and errors were found, it will tell you which repair option is needed to fix it. There are two different repair options and both will require the database to be in SINGLE_USER mode, which will make it inaccessible during the repair:

REPAIR_REBUILD:

ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ([DB_NAME], REPAIR_REBUILD);
ALTER DATABASE [DB_NAME] SET MULTI_USER;

The REPAIR REBUILD option will repair any errors that will not cause loss of data.

REPAIR_ALLOW_DATA_LOSS

ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ([DB_NAME], REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [DB_NAME] SET MULTI_USER;

The REPAIR_ALLOW_DATA_LOSS option will repair errors that may cause loss of data.

MySQL

How to check the integrity of a MySQL database

Microsoft Access

How to check the integrity of a Microsoft Access database