In an SQL Server database, both the MDF and NDF files store information in the form of pages of 8kb each. Here, a page is a basic storage unit in the SQL Server database. If any of these pages become corrupt, they will make the database inaccessible.
In any such circumstance, even the administrator with all the privileges cannot access the data stored on the server. In technical terms, you can call this issue as page-level corruption. Fixing this problem at the earliest is the only way to save your data from permanent damage. Some worth trying options to resolve this issue include:
- Restore the database from the last backup if it is a small one
- Restore the specific corrupted page if the database is big
- Using T-SQL Commands to perform manual recovery
- Use a third-party SQL repair software like Stellar Repair for MS SQL to repair the SQL database server
Reasons behind Page-level corruption
Before proceeding with the solutions, you should find the root cause behind the page-level corruption. Prominent of these factors are:
- Hardware malfunction
- Power supply issues
- Installation of an infected software
- Malware or virus attack
- Installation of the wrong software update
- An undiagnosed server problem
- An accidental deletion of the database
Methods to fix page-level corruption
Let’s learn more about multiple ways to deal with page corruption in a SQL Server databases
1. Restoring database from a recent backup
If the database is small or only a few pages are corrupted, you can restore it from the previous backup. Here are the steps:
- Open SQL Server Management Studio (SSMS)
- Object Explorer àRight Click Databases à Choose Restore Database
- Provide the requisite information and click OK to proceed with restoration
2. Restoring a specific page
In the case of a large-sized database, it would not be feasible to restore it completely. A better option would be to figure out the corrupted page and restore it. For this, follow the below steps:
- Object Explorer à Right Click Databases
- Select Task àRestore Page
- Click Add Button, provide the Page ID and File ID.
- Select the backup file
- Click OK
3. Use T-SQL command for Manual Recovery
Due to the risk-prone manual process, it is better to create a backup of your database or copy the physical files. In this method, notice the role of trace flag.
Admins use Trace flags to find performance issues, fix stored procedures or to deal with an issue that affecting a particular workload adversely. For instance, Trace flag 3604 redirects the output of DBCC commands to the window. When running a DBCC Command, you will have to enable the trace flag 3694 to get the required output.
Next, follow the below-mentioned steps to proceed with the manual fixing of page-level corruption.
- Check the difference between the original file and the corrupted file with the help of a comparison tool.
- Run the DBCC CHECKDB command on the damaged file to find the location of corrupted areas. This will also suggest the least data repair requirements
- Use DBCC to switch on the trace flag 3604.
- Run the DBCC page command to find the data contained in the infected page as mentioned below:
DBCC TRACEON (3604)
DBCC PAGE ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
Here is the description of the printopt parameters
0: Prints the Page header
1: Prints page header and per row hex dumps
2: Prints page header and complete dump of page hex
3: Prints page header and detailed interpretation of each row
- Next, you will need to confirm the physical offset and page number. For this, try to read the table through the below command
SELECT * from dbo.tablename
- After the confirmation of the corruption location, compare the damaged file with the latest backup of the original file. You can use a text comparison tool for this task.
- To make your work easier, copy and paste the corrupted pages from the damaged file to the text comparison tool
- Side-by-side comparison will help you find the root cause behind the corruption
- After finding the errors, fix the corrupted pages, restore the file, and run the DBCC CHECKDB command on it.
- Zero issues in the recovered file will indicate correct restoration and proper fixing of the corrupted file.
Limitations of the manual method
This method can remove page-level corruption in the SQL Server database but will also accompany several drawbacks:
- Needs a high degree of technical expertise
- Consumes considerable time
- Fault-prone direct file editing method involving checksum calculation on every page.
- Improper copy-paste can cause input-output errors, leading to issues in database access.
- Multiple corrupted locations will infect other page types as well. The SQL server cannot open the file as a result.
Using a third-party software
The manual methods entail several drawbacks, posing a setback to the proper working of the database. As a suitable alternative, DBAs prefer to confide in the automated solutions to deal with the page corruption and repair the SQL database server. Stellar Repair for MS SQL is a widely embraced tool in this regard.
The software uses advanced algorithms to fix database corruptions and recover inaccessible data from MDF and NDF files. It works actively in dealing with corrupted files while preserving the integrity of the database.
Steps to database recovery via Stellar Repair for MS SQL
Be it a minor problem or a major page-level corruption issue, the SQL repair tool can fix the damage easily and in the least possible time. Here are the simple steps to recover the database with the help of this software:
- Download and install the software from the official website.
- Launch the Stellar Repair for MS SQL software
- On the first screen, select the database (with .df extension) that you wish to repair
- Click the Repair button to proceed with the repairing of your corrupted database
- Get a preview of the selected database
- Save the recovered data into an existing database, a new database, or in a different format, such as XLS, CSV, or HTML.
To start with, you can try the trial version of Stellar Repair for MS SQL. You can go on to buy its premium version once you find it useful to protect your SQL server database from corruption.
Conclusion
Page-level corruption in a SQL Server database is the worst nightmare for database administrators as it makes the files inaccessible. This directly affects the working of data-centric businesses. To deal with this issue, you can adopt several manual recovery methods. Nevertheless, these ways are highly prone to errors, take too much time, and need the person to have in-depth technical knowledge.
Using A third-party tool to repair SQL database server, such as Stellar Repair for MS SQL would be ideal to tackle these issues. The vastly preferred software can restore all database objects and recover deleted records from the database. Moreover, it can save up to 8 tables simultaneously through parallel processing techniques as well.
Feel free to click through the Stellar Info website to learn more about this software. You can download the trial version of this tool free, which allows you to scan and preview recoverable SQL server database objects.