If you have worked with SQL Server long enough, chances is that one day in your life when you will be faced with the dreaded event of “Database corruption “. Database corruption is something so common nowadays in our IT industry. No matter which technology or product is being used, at some point in time they all become victim to Database corruption.
The most common causes for database becoming corrupt are:
- SQL server crashes/stops unexpectedly due to any reason (Server Crash, OS Crash etc.) Hardware malfunction like RAID Controller Failure Blue Screen of Death (BSOD) Windows OS Upgrade / Re-installation Hardware/Software compatibility issues (like drivers)
- Anything and everything can cause SQL Server to shut down abruptly, resulting in the loss of data and corruption of database.
- Recovering a corrupt database can be done in many ways and it really depends on the type and the level of corruption that your database is currently suffering from. This blog will cover all different types of Database Corruption scenarios and will help you understand how to bring your SQL Server back to life after a crash, server outage or any other reason for data loss.
Database Recovery Flow Chart
In order to get started with restoring our SQL Server databases, you should have a clear picture in mind about what has caused the corruption in the first place. Once you have identified that cause, which could be a hardware failure or a software bug or even due to inadvertent user actions like power failure/surge while DB was being backed up by the DBA, then you will have to first assess which recovery scenario applies to your situation.
The following flowchart shows different possible corruption types and how you would go about fixing them. If you are not able to identify what type of database corruption your database is suffering from, please follow the corruption identification section in this post.
Database Corruption Scenarios
Depending on the nature of problem that caused the corrupt state of database, there are three possible scenarios for recovering a SQL Server Database back to life: Differential backup, Transaction Log backup or Base backup (full/differential).
We will explain all 3 methods here briefly but if you want more detailed information, check out this article:
Differential Backup Recovery
This method works only when the corruption is caused due to a software bug or an issue with user permissions. What happens in this scenario is that after the server crash / data loss, some of the new transactions are executed while some transactions are rolled back. Thus there exists a gap in transaction logs and your good last full backup, which could be only days old (assuming you perform regular full backups), will come to rescue and restore all committed transactions from our good full backup file. The following flowchart shows how we can recover using Differential backup:
Transaction Log Backup Recovery
If your database corruption problem was caused due to Hardware failure like Drive Crash/Controller failure/Server Crash etc., then there is no hope for using differential method since it requires data pages which were written to since your last full backup. Thus, in order to repair this kind of data loss, you will have to restore the transaction logs that are generated after your good full backup file is created which can then be used together with your good differential backup files to recover all committed transactions up till server crash. The below flowchart explains how we can go about repairing database corruption using Transaction Log Backup method:
Full/Differential Backup Recovery
If you were unfortunate enough not able to create either a good Differential or Full Database backup before the server outage, and then fret not! You still have hope for bringing your SQL Server back on-line. This recovery scenario requires restoring Base (Full) Backup followed by one or more of the most recent differential backups. The following flowchart will provide you with more information on how to recover using ‘Full/Differential’ method:
Database Corruption Identification
Once you have finished your last good backup, there are few ways in which you can confirm if the data loss is only limited to the corrupt database or is beyond that. You may follow these quick tips or use any other way as per your convenience for identifying type of corruption problem faced by your SQL Server Database:
- Open up Command Prompt and execute a query against your database from there to see if it gives an error message about table/stored procedure missing. If so, then most probably this block of table/stored procedures has become corrupt and you would be able to restore that table’s data via SQL Server Management Studio using your database backup files.
- If you get query results, then it means that some table/stored procedure has become corrupt but not the entire database structure. You can restore data of these corrupted objects by opening up SQL Server Management Studio and right click on the table/stored procedures which are not working properly and select “Restore…”. Select ‘Point in time” option from there to match your last good backup file or if that doesn’t work then go for Last Log Backup (earliest available) option which will restore all committed transactions since after your last full backup until this log file was generated on the server.
- If no error messages are returned when executing queries against corrupt database, then most probably it is offline or you are using an older version of SQL Server Management Studio which doesn’t support restoring online databases. If this is the case, then follow our article on How to bring a corrupt SQL Server Database back online.
Conclusion:
How to Repair Corrupt SQL Server Database? Any of the above three recovery scenarios should help you restore your corrupt database and getting it back on-line as quickly as possible. If after restoring your SQL Server, users are complaining about other issues like slow performance, high CPU utilization etc.
