Here I am going to share my experience, how I fixed a corrupt database in under 3 Hours. It is really very much important for any Database Administrator or developer to fix the “Corrupt Database” as soon as possible, which can prevent loss of critical data.
Corruption can happen due to many reasons like disk error, Operating system error, isolation errors etc. Today I faced a New Isolation error while trying to use a newly created SQL Server 2012 instance on a Windows Server 2008 R2.
Let’s have a look at some common scenarios wherein corruption can take place:
- A user tries opening tables with full-text indexes and receives the following message: “The Microsoft Jet database engine cannot open the file ‘file location’. The file may be corrupt or you may not have permission to access the file. Verify that the path and file name are correct, that you have read permission on the file, then try again.”
- The below error message is received when users try to use Microsoft Excel 2010 to open an xlsx workbook: “There was an error opening this document”. The range of cells containing the error is displayed.
- A user tries to attach an .mdf database file for which they no longer have permissions and receives the following message: “Cannot attach the database. You do not have sufficient permission to access this item.”
- Many times we found corruption issue with SQL Server 2008/2008R2 databases while migrating from Windows XP machines where disk write issues could arise due to older machines and old OS.
- These all errors occur when we try to access the file, which is corrupted or not present on the disk and the SQL server engine throws an error and stops responding. As a result, users cannot access their data and applications hangs / crash. So let’s see how we can fix these corruptions as soon as possible:
In this article I will show you following steps to recover from “Corrupted Database”: Recover Accidentally-Deleted Files Fix Corruption in SQL Server Databases Recover Damaged Data from Windows NTFS Partitions How to repair Modifications keys in InnoDB tables Rebuild Indexes for Corrupt Tables Reclaim Unused Space by Removing Fragmentation
4 Steps to follows for fixing Corrupted database
Step 1: Recover Accidentally-Deleted Files
The first step is to enable the “Show Deleted Objects” option in Object Explorer. Once you’ve done this, right-click on the Database folder and select Restore -> Show Deleted Objects. This will show all the objects that have been marked for deletion but not yet removed by a garbage collection process. Now restore these files or delete them back to recover your accidentally deleted files. For more information about the Restore command please follow http://technet.microsoft.com/en-us/library/ms178068 (v=sql.105).aspx
Step 2: Fix Corruption in SQL Server Databases
Many times corrupted databases are caused due to transaction logs overflowing or truncation errors. In the below example I have a database called “Database1” which was throwing errors
Step 3: Error is thrown while accessing Database1
In order to fix this issue, I need to stop the SQL Server service, delete all the Transactions logs from the specified path. Once deleted, start the SQL Server services back and now try to access your DB. By following these steps only we can resolve corruption issues with our databases very quickly without any data loss.
The health of Database1 is Normal after fixing Corruption issues
All tables are accessible successfully
Step 4: Rebuild Indexes for Corrupt Tables
The third step will be rebuilding indexes for corrupt tables. If you delete any rows from a table and re-create an index, or rebuild the index, the structure of the index will be different than it was before. Re-creating or rebuilding an index does not recreate all columns in each leaf level of the index.
Conclusion:
In order to fix corruption issues in SQL Server databases, I would recommend you to follow all 4 steps in the above article and recover your data in no time. Corruption is a common issue that occurs with SQL Server Databases and we need to take care of this issue immediately so that we can avoid causing any major harm to our system/data. These are some quick recovery steps but for complete recovery, we will need professional help or better go for third-party tools such as Stellar Phoenix SQL Recovery.
