How Stellar Repair for MS SQL Fixed Corruption in MS SQL Server Database

Introduction

As an Architect, I came to understand that some user was having issues while accessing a Customer Relationship Management or CRM database. In fact, the major problem was that the database was not loading properly - both in SQL application and SQL Server Management Studio. A review of SQL Server error logs revealed that the database was corrupt. CRM is a critical part of critical production databases, hence our prime objective was to get the SQL Server database back online.

There were two major issues to take care of:

1. How to resolve SQL Server database corruption
2. How to reduce server downtime

While creating MS SQL Server backups, we turned on the ‘RESTORE VERIFYONLY’ option to verify if the database can be restored through backup. However, we realized that the SQL Server database was obsolete. Therefore, restoring it from backup was ruled out.

SQL Server database corruption occurs due to various reasons such as corruption in Tables, Index, or Metadata, Data Page Level corruption, or SQL Server errors. These errors can be detected in SQL Server Error Log or through Database Console Command (DBCC) CHECKDB. We tried a few commands to check and repair corruption errors in the SQL database:

1. DBCC TABLE CHECK - to check logical and physical consistency of database objects and provide results at database page level
2. DBCC TABLE REPAIR - to repair specific object errors
3. REPAIR_REBUILD - to repair SQL database object errors at the individual level

Unfortunately, like other SQL Server professionals, we were not comfortable with the usage of Database Console Commands. This is due to the following reasons:

1. DBCC CHECKDB commands do not give accurate results
2. Using REPAIR_ALLOW_DATA_LOSS may result in loss of critical data
3. There is no way to verify if the database is repaired
4. If the database is not repaired in time, it may spread to multiple objects

A downtime is always costly and impacts a business functioning, and may result in loss of productivity and business hours. As SQL Server administrators, our priority was to repair the SQL database without prolonging the server downtime as well as to take care that there is no loss of SQL data.

When the SQL Server database corruption error did not resolve through restoring from backup and Database Console Commands, we tried to fix the issue with the help of MS SQL Database repair tool.