Microsoft SQL server database can be corrupt or damaged due to any reasons at any times. Here are some possible reasons that are responsible for SQL database corruption:
- Platform Issue – 95% SQL database corruption occurs due to this
- SQL server or driver bugs
- Sudden power failure or virus attack
- Hardware issue
If this happen with you then this How-to is beneficial for you.
The best and effective solution to fix any types of SQL server corruption issue is restoring the database from clean and updated backup. If you don’t have backup of the corrupt database then follow below steps:
Step 1: Run DBCC CHECKDB on the corrupt database
DBCC CHECKDB (Name of corrupt database)
Note: You can also specify options: infomsgs, no_infomsgs with DBCC CHECKDB
Step 2: Look for the index id. If index id is greater than one then you can safely dropped and recreate it.
Step 3: If index id is 0 or 1 then you need to re-run DBCC CHECKDB with recommended suitable repair options: repair_fast, repair_rebuild, & repair_allow_data_loss.
DBCC CHECK (name of corrupt database, repair_fast)
DBCC CHECK (name of corrupt database, repair_rebuild)
DBCC CHECK (name of corrupt database, repair_allow_data_loss)
Step 4: Finally run DBCC CHECKDB to ensure that there is no anymore corruption in the database. Similar message will be display on the screen.
DBCC CHECKDB found 0 allocation errors and 0 consistency errors in ‘name of your corrupt database’.
Conclusion: Always create backup of database. It is recommended to create more than one backup because we are rely on hardware to maintain the backup of database but hardware is also prone to corruption. So you need to create backup on tape or removable drive.
