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.
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.
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.
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.
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.
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.
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.
Related Articles
SQL Error 5171: MDF Is Not A Primary Database File
Restore Database from MDF and LDF Files in SQL Server 2017/2014/2012
How to Repair SQL Server 2012 Step by Step
SQL Recovery Program Download: Repair SQL Server Database