How to Automatically Repair SQL Database Page-Level Corruption

Pages are the basic storage units in a SQL Server database. All information stored in the database files (MDF and NDF) is stored in pages. Page corruption is one of the worst issues for most DBAs, as it can corrupt your MDF and NDF files and make the data within them inaccessible.

If you're looking for an effective solution to fix SQL page-level corruption, you can try either of these two methods. One is to use a professional MS SQL Recovery tool to automatically repair corrupted MDF/NDF files. The other option is to manually repair each page individually, which requires experience and a deep understanding of SQL Server.

Causes of page corruption in SQL Server

Corruption in SQL databases can happen anytime, and page-level corruption is no exception. By understanding the causes, we can better prevent and address the issue.

• Hardware failure
• Sudden power outage
• Virus/malware attack
• Failed SQL Server upgrade
• Issues with the server itself

Once you notice that your SQL database is corrupt and not functioning properly, you should immediately back up all the data in your database and start with the repair and recovery process.

Methods to Automatically Repair SQL Database Page-Level Corruption and MDF Files

Fixing page-level corruption in SQL Server databases can be a daunting task for many new database administrators. To repair SQL page-level corruption as soon as possible and prevent further data loss, we highly recommend trying a safer and more effective solution first, such as using a SQL repair tool. One of the most popular software options is the MS SQL Recovery Tool, which offers users the following outstanding features.

• Repairs corrupt SQL database files (MDF/NDF files)
• Recovers deleted records from SQL databases
• Repairs corrupt components of SQL Server databases – Tables, Triggers, Indexes, Keys, Rules, and Stored Procedures
• Enables export of repaired databases to CSV, XLS, HTML, and MDF formats
• Supports MS SQL Server 2017, 2016, 2014, 2012, 2008, and earlier versions

Step 1: Stop the MS SQL Server service

Press "Windows + R" and type in services.msc.

Locate and double-click SQL Server (Named Instance).

In the Properties window, click Stop to stop SQL Server, and then click Apply to confirm.

Step 2: Run the SQL repair tool. On the main interface, click “Browse” (two dots) to select the corrupted MDF/NDF file. Thereafter, click “Repair” to start analyzing your MDF/NDF file.

If you know where the file is located, click Browse to locate the database.

If you do not know the location, click Search to search for the .mdff or .ndf file.

Step 3: Once the scan is complete, all database objects are displayed on the left pane. Select the database object that you want to repair and click on 'Export'.

Select Database Objects

Step 4. Choose your preferred method for exporting the database data: “Export to Database” or “Export as SQL Script.” If you choose “Export to Database,” you will need to supply server information, log in to your account, and select the target database, which can be a new or existing one.

Step 5: Restart SQL Server before clicking OK.

Press the "Windows Key + R" and type in services.msc.

Locate and double-click “SQL Server (Instance Name).”

In the Properties window, click Start to restart the SQL Server service, and then click OK to confirm.

Step 6. Click 'OK' to save the repaired file to the SQL database of your choice.

Utilize the DBCC CHECKDB command to repair page-level corruption issues in a SQL database with UTF-8 encoding.

If you don't want to use any third-party software to fix page-level corruption in SQL Server, you can use the DBCC CHECKDB command to check for database corruption. However, it's always advisable to back up your SQL databases first before doing anything in case something goes wrong.

First, download a text-comparison tool and a data-comparison tool from the Internet. These will help you see the damaged data alongside the original.

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

2. Run the DBCC CHECKDB command on the corrupted file. This command checks the database files, identifies the corruption, and recommends the minimum data repair requirements.

To examine the contents of the affected page, run the DBCC PAGE command. First, turn on trace flag 3604.

**Enable DBCC Trace Flag 3604** **DBCC PAGE ('dbname' | dbid, filenum, pagenum [, printopt={0|1|2|3}])**

In the preceding command, filenum and pagenum are the page IDs relative to the system table. The other parameters, 0, 1, 2, and 3, have the following meanings:

• 0 – This prints the header for the page
• 1 – This is the header for the page with a hex dump of each line
• 2 – This is the header for the page with a detailed hex dump of the page
• 3 – This is the header for the page with an exhaustive explanation of each line

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

SELECT all columns from dbo.tablename

5. Copy and paste the corrupted pages from the damaged file into a text comparison tool.

Use a data comparison tool to compare the damaged page with the most recent version of the original page. This will clearly highlight the problems.

7. Run the DBCC CHECKDB command on the restored files to fix page-level corruption in SQL Server.

Why not recommend this manual approach?

• This method might be useful for minor corruption. If there are multiple damaged locations, it can affect other types of pages, preventing SQL Server from opening the file.
• Direct file editing is prone to errors. Pages are checksummed, so incorrect copy/paste can result in I/O errors or inability to open the database.
• This method is very time-consuming and requires technical knowledge and experience.

End, conclusion

Many users fail to fix page-level corruption in SQL Server manually due to complex procedures. Hence, it's advisable to opt for an automated solution, such as using the MS SQL recovery tool mentioned here.