How to Repair SQL Database Page Level Corruption?

If you work as an SQL server administrator, your life won’t be bug-free. While the problems you encounter might be few and far-flung, the worst and most worrisome problem of all is the SQL database page-level corruption. Page corruption in SQL Server is nothing short of a nightmare, putting all critical server data at serious risk. Even administrators with full access are unable to retrieve the affected data, leading to major disruptions in the organisation’s workflow.Finding out as much as you can about corruption, its causes, and solutions is the only way out of it.

Reasons Behind Page-Level Corruption in SQL

In an SQL database, the fundamental unit of storage is a page, with all data stored in page format. SQL Server database files typically use the MDF and LDF extensions. Essentially, all LDF and MDF files are logically divided into several hundred pages, with each page having its location on the server. In the event of page corruption in SQL Server, each of these pages becomes infected. Because it is easier to fix small chunks of data than the whole file, it is generally recommended to fix individual pages before you try to fix the file.

However, before attempting to fix a problem, identifying its root cause is essential. So why does SQL page-level corruption happen?

1. The primary reason for page-level corruption in SQL is hardware failure. If there is a problem with the hard disk, it is the most likely reason for the corruption. Therefore, you must regularly monitor your hard disk, the server box, and other hardware in the system to avoid facing a problem like this.
2. You must ensure that the power supply to the server is regulated. Power supply problems when the database is running can also lead to page-level corruption.
3. Take care while downloading new software onto the server. If a virus or malware infects the server, it too could lead to damage.
4. While we know that one of the best practices for maintaining a healthy server is to keep it updated with the latest patches, installing the wrong updates can also, unfortunately, cause corruption.
5. Sometimes, a problem in the server itself that cannot be diagnosed can potentially lead to corruption. There is, unfortunately, nothing that you can do about this, except perhaps keep a regular check on the functioning of the database.

If, due to any of these reasons, you start noticing glitches within the server and suspect corruption, you should take a backup of all data on the database as it is and initiate remedial procedures.

Fixing SQL Database Page-Level Corruption

Again, before starting, take a backup of the original MDF and LDF files. This will give you a reference point from which you can restore the database. Also, download a text comparison tool and a data comparison tool from the Internet. These tools will help you compare the corrupted data side by side with the original data.

Follow the steps:

1. Use a text comparison tool to check the differences between the original file and the corrupted file.
2. On the corrupted file, run the DBCC CHECKDB command. This command will check your database file, display the location of the problem areas, and suggest the minimum requirements for data repair.
3. To inspect the data within a corrupted page, use the DBCC PAGE command. Switch on the trace flag 3604 first.

DBCC TRACEON (3604)

DBCC PAGE ({‘dbname’ | dbid}, file_number, page_number [, print_option = {0 | 1 | 2 | 3}])

The filenum and pagenum refer to specific page IDs and are associated with different system tables in the database.

The printopt parameters are 0,1,2, 2,3

0 – It will print the page header.

1 – Displays the page header along with hexadecimal dumps for each row.

2–page header with complete page hex dump

3 – Page header with detailed per-row interpretation

• To confirm the page number and the physical offset, force a logical consistency error and attempt to read the table by running the following command:

SELECT * from dbo. tablename

• Now that the location of the corruption is confirmed, use the text comparison tool to compare the corrupted file with the latest backup of the original file.
• For ease of understanding, copy the corrupted pages from the infected file and paste them into the text comparison tool.
• Use a data comparison tool to analyze differences between the corrupted page and the most recent version of the original page. You will be able to view a side-by-side comparison of the data, with anomalies highlighted.
• Fix the pages and run the DBCC CHECKDB command on your restored file.
  If you have restored and fixed the corrupted data correctly, there should be no anomalies in the recovered file.

Drawbacks of this Method

• If more than one location or a large area is corrupted, other types of pages may be affected, which will cause SQL Server to be unable to open the file.
• Direct editing of the file is a method that is prone to errors. Checksums are calculated on a page-by-page basis, and if you fail to copy/paste correctly, I/O errors will occur, or you may be unable to open the database.
• This solution will require time, and you’ll need to practice it beforehand to ensure its success.

A Better Approach

To address these limitations, many DBAs opt for third-party advanced SQL recovery software. One of the most widely used tools for the purpose is Stellar Repair for MS SQL. Using advanced algorithms, this tool has proven to successfully fix all corruptions of SQL Server databases and recover inaccessible objects from MDF and NDF database files. Users have reported that the software employs a top-tier non-destructive repair algorithm to maintain database integrity. If you’re looking for a reliable and quick solution, this is your best bet.