Checking Database Integrity
How to check database integrity
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.
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:
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.
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.
How to check the integrity of a MySQL database
How to check the integrity of a Microsoft Access database