Fix Page Level Corruption in SQL Server – Top 2 Approaches
In this article, we will discuss quick solutions on how to fix Page Level Corruption in SQL Server and also discuss the reasons due to which it occurs. Continue reading to know more.
There are multiple users who face various issues while working with the SQL Server database. The issue in SQL Server is common if you are a SQL user. One such worse situation is when page corruption occurs in SQL Server. Due to this corruption issue, there are high chance of data loss.
Therefore, in the upcoming section, we are going to discuss a solution on how to fix page level corruption in SQL Server.
Before that, let us explore several reasons behind page level corruption in SQL Server.
Page Level Corruption in SQL Server – Reasons
Here are some reasons for getting page level corruption in MS SQL Server:
- Hardware Malfunction: If there is an issue with your hardware, then this error could occur. For that, you must regularly monitor the hard disk, server box, and the hardware box so to avoid facing these problems.
- Power Outage: Irregular power supply could be the reason of page level database corruption.
- Virus Attacks: You must take care when downloading a new software to your computer system. There might be some chances which require software or file that may be affected by a virus. This can even lead to the damage in SQL Server.
- Irrelevant Updates: It is best doing regular update installations with the latest patches. Ensure relevancy before going for an update installation because the wrong update can lead to the corruption.
Due to these reasons, you may find glitches within the SQL server that may result in SQL Server database page corruption. For resolution, take a backup copy of your database and begins with the initial remedial procedure.
Also read: Recover Table Data and Schema from Corrupt SQL Database
Fix SQL Server Page Level Corruption – Manual Approach
Go through the following instructions that will help to fix page level corruption in SQL Server issue. Let us have a look:
- Initially Identify Corruption in SQL Using DBCC CHECKDB
There are multiple ways to identify the corruption in SQL environment. The first, and the most widely known method is DBCC CHECKDB. Command performs data integrity checks using each datafile and identify the areas of concern. It will come with three major options for recovery i.e.
- REPAIR_ALLOW_DATA_LOSS
- REPAIR_REBUILD,
- REPAIR_FAST
These options are not ideal, mainly REPAIR_ALLOW_DATA_LOSS, as it is minimum level of corruption in SQL database. Even though it is possible to restore without any data loss using these utilities.
- Way to Repair Page Level Corruption in SQL Server
Before taking the backup of original MDF and LDF files in SQL server. This will provide you a reference from which you can easily recover the database.
Here, you can follow the below-described steps to fix SQL Server database page corruption:
- Use the text comparison tool to check the difference between an original and the corrupted files
- On a corrupted file, you need to run the DBCC CHECKDB command. This command will identify your database, display the path of the problem areas, and suggest various requirements for data recovery.
- To examine all the contents of infected page, run DBCC PAGE command. First, you have to switch on the trace flag 3604.
Following parameters are 0,1,2 and,3:- 0 refers to print the page header.
- 1 refers the Page header with row hex dumps
- 2 signifies the page header with complete page hex dump
- 3 indicates the Page header with detailed per row interpretation
- To verify the page number and physically offset, force the logical consistency error and try to read out the table by running the below command:
select * from dbo.tablename- Now, the location of corruption is verified, use a text comparison tool to compare the damaged file using the backup of an original file.
- To understand this, copy a damaged page from an infected file and paste it in the text comparison tool.
- Use the data comparison tool to compare a corrupt page with the latest version of an original page. You can also view the comparison of data-parallel with the anomalies highlighted.
- Fix such pages and run this DBCC CHECKDB command to your recovered file.
- If you have recovered or fixed the corrupted data properly, there are no anomalies in the restored file.
Limitations of Manual Approach
- This procedure is very long and time-consuming while fixing the page level corruption in SQL server.
- If more than one area is corrupted or damaged, then few other pages may be affected that will cause the SQL Server and unable to open the database file.
- Direct editing of a particular file is considered as a method that is fault-prone. Thereby, checksums are calculated on the basis of a few pages and if you will become fail to copy or paste it correctly, I/O errors occur or you will be unable to open the database file.
Page Level Corruption in SQL Server – Resolve by Alternative Approach
To overcome such drawbacks of the manual solution, users can take help of an automated solution named as SQL Database Repair tool .
It is designed in such a way that it can repair corrupt MDF as well as NDF SQL database file easily. The tool allows to fix page level corruption issues in SQL server without any data loss. It has a simple and user-friendly interface to recover database from corruption of SQL version 2019, 2017, 2016, 2014 & its below version. Also, it provide options to export recovered data as SQL Scripts, CSV file format, or into SQL Server present in the same machine or a server network.
Bottom Line
In the above discussion, we have mentioned two approaches by which you can fix page level corruption in SQL Server. Thus, you can wisely select any approach that will help to resolve database corruption issue in an absolute way.