How to Fix SQL Database Page Level Corruption Automatically
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 Qiling 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 Qiling MS SQL Recovery, which offers users with the following excellent features.
- Repairs corrupted SQL database files (MDF/NDF files)
- Recovers deleted records from SQL Database
- Repairs corrupted SQL server database components - tables, triggers, indexes, keys, rules & stored procedures
- Allows to export repaired database to CSV, XLS, HTML, and MDF formats
- Supports MS SQL Server 2017, 2016, 2014, 2012, 2008 and older versions
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 Qiling 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 Qiling MS SQL Recovery here.
Related Articles
- [Fixed] SQL Error 945 | Database Cannot Be Opened due to Inaccessible Files
- MS SQL Database Restore with Recovery and Norecovery Options
- Restore Database from MDF and LDF Files in SQL Server 2017/2014/2012
- SQL Recovery Program Download: Repair SQL Server Database