Question

How do I recover or repair the database if it ever becomes corrupted?

Common Causes of Corruption

  • Sudden shutdown of computer, typically due to power loss or other cause.
  • Failing hard drive in the computer or bad sectors on the drive.

Answer

This article should not be used as the ultimate guide for how to handle corruption or data recovery for your SQL database. Every case is different and needs to be analyzed by a trained technician to determine which solutions are going to be best to attempt to repair or recover your database, below are a list of common remedies for corruption.

  • Repair the corruption to the database using DBCC CheckDB() which is built into SQL Server.
  • Restore a recent backup of your SQL Database. The main downside here is that any data from the time of the last backup till the time of the crash/corruption will be lost.

Tips

  • When the database is corrupted you generally will not be able to create a backup of the corrupted database. The natural action is to detach the database from SQL so you can manually backup the MDF (data) and LDF (log) files for the database in question, do not do this because you will likely not be able to reattach the database since it is corrupted currently. To make a backup, stop the SQL Server service then perform a manual copy/paste of the MDF and LDF files for the database.
  • Once you have a backup of the MDF and LDF files, it is recommended that you compress those files into a RAR or ZIP format and copy them onto another system. When dealing with corruption due to a suspected hard drive failure it's important to get a copy of the data to another machine or location as quickly as possible in the event that you have a total failure of the computer.

Version Information

  • Entered : Brandon Readlinger, 8/26/2014

See Also