SQL Server database corruption can be a nightmare for any SQL Server professional. It can lead to compromised data integrity and availability, resulting in significant business consequences. In this article, we will discuss the diagnosis process for SQL Server corruption and explore a method to correct page-level corruption without using the REPAIR options with the DBCC CHECKDB command.

Diagnosis – SQL Server Corruption

In the event of SQL Server database corruption, it is crucial to have contingency plans in place. This may include disaster recovery plans, replication, database mirroring, clustering, and other strategies. However, there are scenarios where these options may not be suitable.

Imagine a situation where you have a 20GB database with customer records and transaction data. You rely on a robust backup model to protect your data, without using mirroring, replication, or clustering. Suddenly, you encounter an error message indicating database corruption. This severe error threatens database integrity and requires immediate attention.

At this point, the first instinct may be to restore the database. However, the restoration process can be time-consuming and complex, especially if you have a large database and limited recovery time objectives (RTO). Additionally, there is a risk that the corruption may have existed before the error message, potentially affecting your backups as well.

DBCC CHECKDB

Fortunately, SQL Server provides a utility called DBCC CHECKDB, which allows you to perform systematic data integrity checks throughout the data files and identify areas of concern. This utility offers three main options for recovery: REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, and REPAIR_REBUILD. While these options can help restore the database, they are not ideal and should only be used as a last resort.

In this article, we will explore an alternative method to repair a corrupt data file using direct editing of the database files. This method requires access to a clean copy of the database, either from a QA/Test/dev stack or a restored copy of an old, clean backup.

Preparation

Before starting the repair process, you will need a few tools:

• Notepad++ (open-source text editor)
• Hex Editor Neo (freeware hex editor)
• SQL Server Management Studio
• Text comparison tool (e.g., online text diff tool)

Ensure that you have full access to the SQL Server data files and sysadmin access to the SQL Server instance. You will also need a clean backup of the database without any corruption.

Fixing the Corruption

The first step is to examine a healthy data file in a hex editor to understand its layout and structure. This will help you identify the corrupted portion of the file. Once you have identified the corruption, you can force a logical consistency error by attempting to read the affected table. This will provide you with the physical offset address of the corruption within the file.

With the physical offset address, you can compare the corrupted page with a valid copy of the page from the clean database. Using a data comparison tool, you can identify the specific bytes that have changed and need to be replaced in the corrupted file.

Once you have made the necessary changes, save the corrupted file and set it back online in SQL Server Management Studio. Finally, run DBCC CHECKDB to ensure that there are no anomalies with the data.

Conclusion

SQL Server database corruption can be a challenging issue to handle. However, with the right tools and techniques, it is possible to diagnose and fix corruption without resorting to drastic measures like data loss. By understanding the structure of the data files and using direct editing methods, you can recover your database and ensure data integrity.

Remember to practice these methods beforehand and have a solid backup strategy in place to minimize the impact of corruption on your SQL Server environment.