How to Fix SQL Database Page Level Corruption Automatically

Cedric updated on Feb 22, 2023 to MS SQL Database Recovery | How-to Articles

MS SQL Database page level corruption will occur due to many reasons. If you happen to meet this problem, try to fix page level corruption in SQL database with the two ways in this article: either use EaseUS MS SQL Recovery to repair corrupted MDF/NDF automatically, or adopt a manual method.

A page is a basic unit of storage in SQL server database. The database files (MDF and NDF) carry all the saved information in the form of pages. The page level corruption is one of the worst problems for most DBAs, which will cause both the MDF and the NDF files corruption and make the data saved in database inaccessible.  

If you are looking for effective solutions to fix SQL page level corruption, try the two ways provided below. One is using professional EaseUS MS SQL Recovery to repair corrupted MDF/NDF automatically, and the other is using a manual method to fix each page one by one, which requires experience and deep knowledge of SQL Server.

Causes of SQL Server Page Level Corruption

Corruption in the SQL database can occur at any time, so as to the page level corruption. By understanding the causes of the problem, we can better prevent and solve the problem.

  • Hardware malfunction
  • Sudden power outage
  • Virus/malware attack
  • SQL Server upgrades failure
  • A problem in the server itself

Once you begin to notice that your SQL database gets corrupted and runs abnormally, you should instantly back up all the data in the database and start fixes and recovery.

Automated Way to Fix SQL Database Page Level Corruption and Repair MDF

Fixing page level corruption in SQL server database is a tough job for many new database administrators. In order to fix the SQL page level corruption as soon as possible and avoid more serious data loss, we highly suggest that you should try a safer and more useful solution at first, that is applying a SQL repair tool. One of the most popular software is EaseUS MS SQL Recovery, which offers users with the following excellent features.

Step 1. Stop MS SQL Server service

Press "Windows + R" and enter services.msc.

Find and double-click "SQL Server (instant name)".

In the Properties windows, click "Stop" to end the SQL Server and click "Apply" to confirm.

Step 2. Run EaseUS SQL repair tool. In the main interface, click "Browse" (the two dots) to choose the corrupted MDF/NDF file. Then click "Repair" to start analyzing your MDF/NDF file. 

If you know the exact location of the file, click "Browse" to locate the database.

If you don’t know the file location, click "Search" to search for the .mdf or .ndf file in.

Step 3. When it has done, you will see all the database objects listed in the left pane of the window. Choose the database objects you'd like to repair and click "Export".

Step 4. Choose a preferred way to export the database data: "Export to database" or "Export as SQL scripts". If you select "Export to database", you need to further select the server information, log into your account, and select a destination database, either a new one or an existing one.

Step 5. Before you click "OK", now you need to restart the SQL Server.

Press "Windows + R" and enter services.msc.

Find and double-click "SQL Server (instant name)".

In the Properties windows, click "Start" to restart the SQL Server service and click "Apply" to confirm.

Step 6. Click "OK" to save the repaired files to your desired SQL database.

Fix SQL Database Page Level Corruption with DBCC CHECKDB Command

If you don't want to use the third-party software to fix the page level corruption in SQL Server, you can use the DBCC CHECKDB to check the database corruption. But you'd better back up your SQL database once the corruption occurs as a precautionary act.

In the beginning, download a text comparison tool and a data comparison tool from the Internet. These tools will help you see a side-by-side comparison of the corrupted data and the original data.

1. Use a text comparison tool to check the differences between the original file and the corrupted file.

2. Run the DBCC CHECKDB command on the corrupted file. This command will ensure check the database file, identify the defects and advise the minimum requirement of data repair.

3. To examine the contents of the infected page, run the DBCC PAGE command. Switch on the trace flag 3604 first.

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

In the above command, filenum and pagenum are page IDs which are related to system tables. The other parameters 0,1,2 and,3 are demonstrated below.

  • 0 – This will print the header part of the page
  • 1 – It is the page header with per row hex dumps
  • 2 – It is the page header with detailed page hex dump
  • 3 – This is page header with elaborated per row interpretation

4. Use the following command to confirm the page number and the physical offset, force a logical consistency error and attempt to read the table.

SELECT * from dbo.tablename

5. Copy the corrupted pages from the corrupted file and paste them in the text comparison tool.

6. Use a data comparison tool to compare the corrupted page with the latest version of the original page. You will identify the glitches clearly.

7. Run DBCC CHECKDB command on the restored file to fix page level corruption in SQL server.

Why Is This Manual Method Not Recommended

  • It is useful for minor corruption. If several locations are corrupted, other types of pages may be affected, which will make SQL Server fail to open the file.
  • Direct editing of the file is error-prone. Checksums are calculated on a page-by-page basis and if you fail to copy/paste correctly, the input/output errors will occur, or you may be unable to open the database.
  • This solution will cost much time and it requires technical expertise and rich experience.

Conclude

Many users will fail to fix page level corruption in SQL server with the manual method because it’s too complicated. Therefore, it is better for you to use an automated approach to resolve the problem, like using EaseUS MS SQL Recovery here.