Repair Corrupt SQL Server Database

-->

In this blog post, I will try to explain all possible steps that you will have required to repair your corrupt SQL server database. Let's consider a case where you are working on SQL server database, trying to access some records from a table. During table access process you have got an error message that specifies, you can't access desired table due to database corruption. Corruption in SQL server tables can happen due to several reasons; some most prominent reasons are virus attack, power outage, improper system shutdown, hardware failure etc.

What to Do Now? It is the first question that will come in your mind. I will suggest some tips at this point.

• Don't Panic
• Keep Patience
• Take Backup of your database
• Check hardware configuration

How to Check Error Message?

Run DBCC CHECKDB command on the database, it will write a reported error message in SQL server error log. The reported error message will show you how many consistency errors are found.

For example: You may get below error message in your error log

2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.

Note: You can run DBCC CHECKDB command on the database any time no matter your database is online or offline but you will get information message only when DBCC CHECKDB will run on online database.

How to Fix Corruption: There are three methods that you can try to repair corrupt table. The methods are....

• Check Hardware Configuration
• Restore from Backup
• Run DBCC CHECK with repair_allow_data_loss

Check Hardware Configuration: First of all check your hardware configuration. If you are not a hardware geek then connect with your system support team and fix the issue.

Restore from Backup: If backup of corrupt table is available then restore from updated backup.

Run DBCC CHECKDB with repair_allow_data_loss: DBCC CHECKDB is a free utility offered by Microsoft to repair & recover SQL server database with minimum data loss.

ALTER Database Employee_Details Set Single_User

GO

DBCC CHECKDB (Employee_Details, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS;

GO

ALTER Database Employee_Details set multi_user

GO