Tuesday, 14 July 2015 00:00

Fix Database Integrity Errors in Microsoft SQL Server

Rate this item
(0 votes)

Sometimes you may encounter data integrity issues with one or more tables on a Microsoft SQL server. These errors may be found during a data integrity check using DBCC CHECKDB, or even via general use of the database. There are 3 options to correct the database integrity issues; safe repair, restore, and non safe repair (in that order).

Its always good practice to take an extra backup before any of these commands are run (specifically the non-safe repair option).

Safe repair using REPAIR_REBUILD:

Change the database to single user mode.

ALTER DATABASE <database_name> SET SINGLE_USER

Repair the database using a safe repair that will not cause data loss (if possible).

DBCC CHECKDB ('<database_name>', REPAIR_REBUILD)

Change the database to back to multi user mode.

ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.
Restore the database from a backup:
If the above safe repair does not work, the best option is to restore the database from a backup. Only if a database backup is not available, look at the next option, which is a non-safe repair.

Non-Safe repair using REPAIR_ALLOW_DATA_LOSS:
This option is non-safe as it will repair the database, by simply removing the data associated with the integrity errors. This option will fix the integrity errors, however it may remove needed data at the same time. Only perform this option if there the safe repair doesn’t work, and there is no good backup.
ALTER DATABASE <database_name> SET SINGLE_USER
Repair the database using a non-safe repair that will potentially cause data loss.
DBCC CHECKDB ('<database_name>', REPAIR_ALLOW_DATA_LOSS)
Change the database to back to multi user mode.
ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.

Reference: http://www.networkinghowtos.com/howto/fix-database-integrity-errors-in-microsoft-sql-server/

Last modified on Friday, 17 July 2015 08:57
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Get Help Now

Thank you for contacting us.
Your Private Investigator will call you shortly.