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.
