7 Essential Steps to Recover a Corrupt SQL Server Database

This post - 7 Essential Steps to Recover a Corrupt SQL Server Database - is written by a Guest Blogger. Andrew Jackson (LinkedIn - @AndrewJackson) -  is a SQL  and SQL Server blogger too. He likes to share about SQL Server and the problems related to it as well as their solution and also handles database related user queries, Server or database maintenance, database management etc.

How to Deal with Corruption In SQL Server Database ?

Backups are evidently a DBAs foremost approach when caught in a critical crisis condition. However, there are instances where responding to corruption is a part of dealing with it besides just leaving the server in the affected state and restoring from a backup. Doing this prevents the condition from worsening leaving scope of database maintenance. Corruption is not the end of the situation and there is a sequential order of dealing with the situation, which can save your database from surfacing severe loss. The segment puts light on some of such tips straight from RDBMS experts for the DBAs, in order to help them understand how important ‘carefully’ responding to corruption in SQL Server is, besides just recovering from it. In this approach we will look on some crucial steps which can be helpful in recovering a corrupt SQL Server database.

Steps to Recover a Corrupt SQL database ?

1. Avoid Server Rebooting: Rebooting will only help in resolving some minor level issues at the  OS end and not at the server end. Rebooting of server is not advisable to retain the data for as long as possible. Rebooting makes SQL put the data in offline mode and detect it as SUSPECT. This worsens the condition and lowers the scope of recovery that was earlier there.
2. Rerun DBCC CHECKDB: When there is only a single issue reported or a minor one involved, then it is suggested that the DBCC CHECKDB be rerun. To be more specific, if you have only received a mere of one or two errors then you can actually attempt to re-run DBCC CHECKDB. There is a possibility of such a situation-taking place because the server accidently wrote on a bad spot or the write procedure was mangled and with the DBCC rerun, the server was able to transparently perform the recovery and write to a new instance. It is the rarest of rare cases, yet it does happen, and it has happened with DBAs.
3. Perform Full Recovery: In case of multiple SUSPECT pages in a database, that it seems impossible to repair each of them individually in a time duration lesser than a full recovery then execution of full recovery is suggested. In addition, there are also cases where the critical system pages are complete destroyed, in those cases too, it is advised that a full recovery be opted for.
4. Understand & Respond: In certain conditions, the server hasn’t actually encountered  corruption yet, but is on the verge of getting affected by it. IO errors are an example of the same. Run DBCC CHECKDB in the soonest possible time in the following manner:   DBCC CHECKDB([yourDbName]) WITH NO_INFOMSGS,ALL_ERRORMSGS
5.  Don’t Detach Database: Just as it is not suggested to reboot the server in case of corruption, it is also a bad practice to indulge in detaching or re-attaching of the data from and to the server. When corruption has already taken place, such acts make recovery of corrupt SQL database harder to carry out.         
6. Validate Repairs: When dealing with a corrupt database on an infected server it is highly suggested that repair procedures be first tested and validated for their success ratio. Before implementing just about anything on your database, generate a copy of it and perform the recovery somewhere else. Practice the procedure and only implement it on the original copy if successful outcome is achieved. The production database is always a valuable asset and must not be interfered with.         
7. Size Up Corruption: There have been many instances where DBAs didn’t wait for DBCC CHECKDB to perform completely and proceeded with other approaches.This ended up destroying the data or intensified the downtime duration by neglecting the root causes while responding to symptoms of corruption. Meanwhile, some cases are so minor that simply by  running DBCC CHECKDB only the situation can be avoided. Therefore, it is very important to size up the options once you have all the facts prepared with you accordingly.

Besides knowing what to do when corruption has surfaced on SQL Server, one should also know what not to do to avoid worsening the condition. listed above are a combination of do’s and don’ts to remember while recovering a corrupt SQL Server database.

Observational Verdict: Keeping things level head and understanding the situation as to what is going on with the availability of recent and tested backups, dealing with corruption situations can become difficult but be non-fatal. Regularly practicing the outlined tips, disasters can totally be avoided along with the database kept intact throughout. SQL Server is a huge and massively used relational database management system, which is maintained generally with regular backups and other toolkits for regularly encountered short-term issues. However, In case of failure of above mentioned if one doesn’t succeed in recovering a corrupt SQL Server database, then going with third party tool like SysTools SQL Recovery Tool is helpful to overcome the situation. Recovery is a major step that is only taken by DBA when everything else fails to keep up the database. Thus, when responding to corruption in SQL Server, reboot of the server should be avoided and detaching the database too along with other such tips stated above.

Read more on Database Corruption

Find database corruption with msdb.dbo.suspect_pages (SQL ...

Powershell Script - How to find Event Log entries for IO problems that could cause database corruption

SQL Server – DBCC CHECKDB FAQ (SQL Server DBA)

How to predict DBCC CHECKDB finish time (SQL Server DBA)