Checking Database Integrity - Revision history

Briana at 17:01, 28 June 2012

2012-06-28T17:01:24Z

Davidd: /* MSSQL */

2012-06-28T15:34:57Z

‎MSSQL

Davidd: /* Access */

2012-06-28T14:24:47Z

‎Access

Davidd: /* MySQL */

2012-06-28T14:24:25Z

‎MySQL

Davidd: Created page with "How to check database integrity ==MSSQL== How to check the integrity of an MSSQL database ===DBCC CHECKDB - Checking=== The '''DBCC CHECKDB''' command allows you to check..."

2012-06-28T14:23:39Z

Created page with "How to check database integrity ==MSSQL== How to check the integrity of an MSSQL database ===DBCC CHECKDB - Checking=== The '''DBCC CHECKDB''' command allows you to check..."

New page

How to check database integrity

==MSSQL==

How to check the integrity of an MSSQL 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==

==Access==

[[category:Databases]]

← Older revision		Revision as of 17:01, 28 June 2012
Line 47:		Line 47:


−	[[category:Databases]]	+	[[category:Databases-MySQL]]
		+	[[category:Databases-MSSQL]]
		+	[[category:Databases-Access]]

@@ Line 42: / Line 42: @@
 How to check the integrity of a MySQL database
-==Access==
+==Microsoft Access==
-−
 [[category:Databases]]

@@ Line 4: / Line 4: @@
 How to check the integrity of an MSSQL database
@@ Line 35: / Line 37: @@
 The '''REPAIR_ALLOW_DATA_LOSS''' option will repair errors that may cause loss of data.
 ==MySQL==