How to Repair Corrupt SQL Database & Recover Data from MDF Files

Have you ever met similar experience with your SQL Server: you are unable to access a MDF file that contains many useful records. And it just pops out some wired error messages. After running either the dbcc checkdb command or the dbcc checktable command, it is confirmed that SQL database corruption has occurred with your current using SQL Server 2000/2005/2008 etc. The situation can happen if your SQL has just suffered server errors, database damage, hardware problems, virus attack etc. See possible causes and repair solutions below.

Typical Symptoms

• Extent E_ID in database ID DB_ID is allocated in both GAM GAM_P_ID and SGAM SGAM_P_ID.
• Table error: Object ID O_ID, index ID I_ID, page P_ID, row ROW_ID. Record check (CHECK_TEXT) failed. Values are VALUE1 and VALUE2
• Database corruption occurs after you run the "alter table" Transact-SQL command to change a column from not null to null in Microsoft SQL Server 2000 
• A database is marked as suspect and the database becomes unavailable. 
• Database error: PAGE_TYPE page P_ID for database 'NAME' (database ID DB_ID) is invalid. 
• And more strange error hints.

Possible Causes

• Corruption in the extents due to the irregularities in the IAM, GAM, and SGAM pages. 
• A condition specified in the CHECK_TEXT statement could not be fulfilled. 
• The database table is logically or physically damaged. 
• SQL Server does not synchronize column status between the syscolumns system table and the sysindexes system table. A column is part of a clustered index key that may be null.
• Either the file header page or the boot page is damaged in the mentioned database.
• And more reasons out there.

Resolutions

1. To solve the problems, first of all check if you have backup. If updated SQL database backup is available, then the issue can be resolved easily, try to restore the data from your previously taken valid backup.
2. If you don't have a backup, make sure that the database is online. Then run the dbcc dbreindex command or the dbcc checktable command by using the repair_rebuild option. If the database is marked as suspect, telephone Microsoft Customer Support Services as Microsoft has confirmed that this is a problem in the its products.
3. If that still won't help, then the last option is by relying on a SQL recovery tool to repair SQL database and recover the data back.