Repairing a Corrupted Database

One of the most common reasons behind database corruption is collision with any third-party software. Virus attack or bug infection can also corrupt the files. A hardware fault in your system or a crash in your hard disk drive (HDD) may cause the database files to become corrupt. Or, if the database files are being stored in a compressed volume or folder, this may cause corruption in the database files too. So it should be avoided to store SQL Server database files in compressed volumes or folders.

Consider this scenario:
You have been working in a SQL Server database from last few days. One day you find that the database status is tagged as suspect, which means the database file is corrupted. Or, you are having problem while connecting to the database. So how to fix it?
According to the Microsoft KB "How to troubleshoot database consistency errors reported by DBCC CHECKB", the best solution to fix database consistency errors is to restore from a known good backup. However, if you cannot restore from a backup, then you can try DBCC CHECKDB to repair the error. Below are the steps:

1. DBCC CHECKDB(DBName) WITH NO_INFOMSGS
The DBName is a name of your corrupted database. If this is completed without any errors then the database does not need to be repaired.

2. ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
The database must be set in single user mode before repairing it.

3. DBCC CHECKDB(DBName, REPAIR_REBUILD)
There are number of repair model, you should first try REPAIR_REBUILD, which is no data loss. If OK, go to step 5.e (multi-user mode) If not, go to next step.

4. DBCC CHECKDB(DBName, REPAIR_ALLOW_DATA_LOSS)
This may cause data loss. If ok go to step 5.e (multi-user mode) If not, go to next step.

5.
a. ALTER DATABASE DBName SET EMERGENCY
b. ALTER DATABASE DBName SET SINGLE_USER
c. DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
d. ALTER DATABASE DBName SET ONLINE
e. ALTER DATABASE DBName SET MULTI_USER

However, if you tried all the steps above but still unable to repair the database corruption, you can try a more powerful third-party SQL database recovery software - Stellar Phoenix SQL Database Repair