Learn How to Repair Corrupted Page in SQL Server

Admin | Published: 2021-03-25T12:14:04+00:00 | SQL Server

SQL Server is the widely used database management system that contains information in the form of pages. All the physical data is stored in MDF (primary), and NDF (secondary) database files. These files store information in the number of pages and all the pages are stored at some location. Being an SQL Server user, you need to be careful of all types of SQL database corruption issues. The SQL page-level corruption can jeopardize all the data saved on the server. Hence, it is very necessary to take proper steps to protect the SQL data from such damage. This article explains the causes, and solutions to repair corrupted page in SQL server.
Before that, let us explore the possible reasons behind SQL Server page-level corruption.

Causes of SQL Server Page-Level Corruption

  • If there is a problem with your hard disk, then it is the most common reason for database corruption. And, you need to regularly monitor your hard disk and other hardware in the system to avoid such problems.
  • Must ensure that there is a continuous power supply to the server. If there is an interruption in the power supply when the database is running, it can also lead to page-level corruption.
  • Take care while downloading new software to your system. If the software you are downloading is infected by a virus or malware, it could also lead to damage.
  • Installing the wrong updates can also cause database corruption.

How to Repair Corrupted Page in SQL Server 2019 and Other Versions

This can be done either by manual process or by using the automated solution. Here, in the subsequent section, you will find both the solutions. Read the complete post and resolve SQL Database corruption issues effortlessly.

Manual Method to Fix SQL Server Page-Level Corruption

Execute the below-mentioned DBCC CHECKDB command to fix corrupted page in SQL server. Firstly, find out the corrupted page in SQL Server using the given instructions:

1. Download a text-comparison tool and check the differences between an original and the corrupted data file.

2. Afterward, run the DBCC CHECKDB command on the corrupted files. This command will check the database files completely and identifies the issues.

3. Switch on the trace flag 3604 and execute the DBCC PAGE command to examine the contents of the infected pages.

DBCC TRACEON (3604)
DBCC PAGE ({ ‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3}])

Here, Filenum & Pagenum denotes page ids. Some other parameters are-
0 – It is used to print the page header
1 – It denotes page header with per row hex dumps
2 – It denotes page header with the entire page hex dump
3 – It denotes page header along with per-row interpretation

Now, to confirm the page number, you need to run the below command:

Select * from dbo.tablename

4. Once the location of corruption is confirmed, use the comparison tool to compare the corrupt file with an original backup file.

5. At last, fix the corrupted page and execute the DBCC CHECKDB command on the recovered database files to fix the page-level corruption in SQL Server.

Challenges in the manual approach

  • Highly time-consuming process to resolve corruption issues from SQL Server database.
  • It involves several lengthy and complex queries to be executed.
  • Extremely complex to implement for those users not possessing technical knowledge.
  • Great chance of losing data while executing the commands.

Use Professional Solution to Repair Corrupted Page in SQL Server

Due to the above-mentioned limitations or drawbacks of the manual approach, it is confirmed that this way is completely unreliable for such types of issues. So, the best way to repair corrupted page in SQL server by using a reliable SQL Database Recovery tool. The tool has the ability to fix SQL-based corruption issues and errors flawlessly. Moreover, the tool can efficiently repair corrupt MDF and NDF database files without the risk of any data loss.

Steps to corrupted page in SQL server

Step 1: Download and install this application on your computer.

Download Purchase Now

Step 2: Run the tool and Add the MDF file using the Open button.

Add MDF file
Step 3: Select the appropriate scanning option- for minor corruption use “Quick Scan” and for severe corruption use “Advance Scan”. Also, select the SQL Server version of your MDF file and click the “OK” button.

Scan option
Step 4: Once the tool completes the scanning process, you can view the recovered MDF file data like tables, views, functions, etc. and click the Export button.Preview data

Step 5: Now export the recovered date to SQL Server Database, SQL Server Compatible SQL Scripts, or CSV File Format.

Select Export option
Step 6. Select the required database components and then click the Export button.

Click Export

Concluding Words

In this write-up, we have explained the best methods to Repair corrupted page in SQL server 2019, 2017, 2016, 2014, 2012, 2008 / 2008 R2, 2005, 2000. As per the user’s choice and requirement, they can pick any of the given solutions. If you go with the software solution, you can be assured that no data will be lost and the data integrity will be maintained during the process. It provides you the safest environment to perform the recovery process.