Repair Page Level Corruption in SQL Server Database: A Complete Guide

Summary: Page-level corruption in SQL Server can generate serious problems, like failed queries, unreadable data, or system crashes. This usually occurs when the database file becomes corrupted due to hardware failure, unexpected shutdowns, or disk issues. In this blog, we will show you how to fix page level corruption in SQL Server. From the manual method to a professional SQL Database Recovery Software for an accurate, precise, and hassle-free recovery. Download Now   Purchase Now

What is Page Level Corruption in SQL Server?

Page-level corruption in SQL Server means a single 8 KB data page is damaged or inconsistent. This makes data inaccessible, degrades performance, can destabilize the database, and may lead to data loss. It’s often caused by hardware issues, power failures, software bugs, or file system problems, and is primarily detected using DBCC CHECKDB. Here are some other common reasons:

• Due to incomplete writes may corrupt pages.
• Bad sectors affect .mdf (MDF) or .ndf (NDF) files.
• Sometimes, introduces logical inconsistencies.
• Maybe leaves transactions uncommitted.
• Moreover, it may interrupt I/O operations.

Symptoms of SQL Server Page Corruption

The page-level corruption can create problems, like:

• Error 824: SQL Server detected a logical consistency-based I/O error.
• Queries failing on specific rows or tables.
• Backup/restore errors.
• Transaction log inconsistencies.
• Moreover, sudden VM crashes occur if SQL is hosted virtually.

How to Fix Page Level Corruption in SQL Server? 

Once corruption is identified, you have several options to fix the corruption. Manual method and a professional tool, both are available to resolve this error. If you have minor corruption, go for the manual method. Otherwise, for minor/major corruption, the recommended tool is the best option.

Method 01: Repair SQL Server Database Corruption Manually

If the corruption is minimal and your database is accessible, you can use built-in SQL Server tools like DBCC CHECKDB to repair it. Here’s the step-by-step guide to fix SQL Server page corruption:

Step 01: Run DBCC CHECKDB to Detect Page Corruption

First, scan the database for corruption errors.

DBCC CHECKDB (‘YourDatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS;

This command will check the database for consistency errors and display messages if there’s any corruption.

Example Output:

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error…

Step 02: DBCC CHECKDB repair SQL Server Using REPAIR_REBUILD

If the corruption is not severe (like index issues), try this first:

DBCC CHECKDB (‘YourDatabaseName’, REPAIR_REBUILD);

Method 02: Perform Page-Level Restore in SQL Server (Using SSMS)

If only a few specific pages are corrupted and the rest of your database is healthy, there’s no need to restore the entire database. You can use Page Restore, a feature in SQL Server that allows you to recover only the damaged pages.

Important Note: Page restore is only supported if your database is using the Full or Bulk-Logged Recovery Model.

Steps to Fix Page Level Corruption in SQL Server Using SSMS

Follow these steps to restore corrupt pages using SQL Server Management Studio (SSMS):

1. Primarily, launch SSMS and connect to your SQL Server instance.
2. Then, in Object Explorer, expand the Databases node.
3. Right-click the affected database. Navigate to: Tasks > Restore > Page.
4. After that, open the Page Restore Window:
   • Further, verify the correct database name
   • Select the correct backup file and backup set from which to restore.
5. Next, click on Check Database Pages, and SQL Server will scan the database and display the damaged pages in a grid.
6. Now, use the Add or Remove buttons to choose which pages to restore based on the scan results.
7. Finally, click OK to restore the selected pages only. SQL Server will restore those damaged parts from the backup.

Read Also: Learn How to Repair Index Corruption in SQL Server Database?

Points to Remember:

• Make sure you have a full backup and log backups available.
• Furthermore, the database must be in FULL or BULK_LOGGED recovery mode.
• Page-level restore is not available if the database is in SIMPLE recovery mode.

Limitations of the Above-Mentioned Methods

While manual methods like DBCC CHECKDB and Page Restore can repair minor corruptions, they have several limitations:

• Manual methods may not provide a guarantee of 100% accurate recovery.
• There might be a risk of losing your crucial business data.
• With the manual method, you need to backup your data.
• In addition, they require technical SQL Server expertise to perform.
• Not helpful to repair the majorly corrupted/damaged SQL database file.

Read Also: Best Solution to Fix SQL Server Error 3456 Easily

Method 03: Reliable Tool to Fix Page Level Corruption in SQL Server

If SQL Server is severely corrupted, you can use the Aryson SQL Database Recovery Software. This tool is designed to repair all kinds of SQL Server database corruption, including page-level corruption, in just a few clicks. Whether your .mdf or .ndf files are damaged, this tool can recover tables, views, stored procedures, and even deleted records. Moreover, you don’t need to be a tech expert to use it. With a few simple steps, you can restore your data and get your database back to normal without any risk of data loss or downtime.

Easy Steps for SQL Server page-level corruption Repair:

1. Primarily, download & install the SQL Database Recovery Tool on your system.
2. Next, click on Open and browse the MDF/NDF (SQL) corrupted file.
3. Then, choose Advanced or Standard Recovery Mode and Fix Page Corruption.
4. After that, check all the recovered files and then select them to save.
5. Lastly, you can click on the Save SQL Data option to complete the recovery.

Comparison Between Manual Methods and a Professional Tool

Below is a detailed comparison table to help you understand the difference between using manual methods and the SQL Database Recovery Tool to fix page-level corruption in SQL Server:

DBCC CHECKDB (Manual Method)	Page-Level Restore (Manual Way)	Professional Tool (SQL Database Recovery Tool)
This method requires technical expertise of how to execute the commands.	Similarly, intermediate SQL knowledge is needed.	No need for technical expertise or SQL knowledge to perform the recovery.
Might be a risk of data loss, especially with REPAIR_REBUILD.	Low risk. But, only partial pages are restored.	Provide Recovery without data loss or minimal disruption.
Suitable for minor corruption.	Same (Only Repair low corruption)	In contrast, helpful to recover both minor and major corruption.

Final Thoughts

Fix Page Level Corruption in SQL Server is important because it can affect the integrity of your SQL data. Corrupted pages may lead to failed queries, data loss, or even system crashes if not handled properly. While manual methods like DBCC CHECKDB and page restore are useful for minor issues, they have limitations. For complete and secure recovery, especially in severe cases, using a professional SQL Database Recovery Tool is the best solution.

Frequently Asked Questions (FAQs):-

Related Post