Steps to Fix SQL Server Page Level Corruption

SQL Server contain it’s all data in Primary File, which are also known as MDF file. Every MDF file contains all primary information inside of its equal sized (8182 Bytes) pages. Each page of SQL server consist three sections- Header, body and slot array. When any information becomes changed from any part of page via hardware failure, in that case user will unable to open that page of file. This is called SQL Server page level corruption which may occur due to several reasons.

Therefore, in today’s article we are going to fix page level errors of any database. For this we will create a new database and after that we will corrupt it. When corrupted database show us error then we will solve it through various manual and alternate methods.

Scenario- Let’s Create a New Database

Now, we are creating a new database in SQL server and the name of database is ‘Test’. For creating database with Create database statement, then we are creating a table Example and inserting few records in this table.

Setting Database ‘Test’ Offline

When database ‘Test’ is created successfully, we are setting database offline to perform page level corruption task in that database.

Following is the command to set database offline:

alter database test set offline

By using above mentioned command our database will become offline and when we will refresh database folder, ‘Test’ database will displayed in red coloured icon. It is so because we are making some changes in database to make it corrupt.

Corrupting Database ‘Test’ Using Hex-editor

We cannot make any changes in database, when it is online. It is so because the SQL Server will not allow us to make any permanent changes in online mode of database. So, for making changes in database we are setting it in offline mode. Now, we are opening the database file test.mdf via hex-editor and making some changes in Strings of it and then Saving the changes.

After making string changes we are again setting database in online mode to check whether the database is accessible or not.

Setting Database Again in Online Mode

Now, set database online again and use following command for it:

alter database test set online

When database become online, we are executing below command again to test weather the database is corrupted or not:

Select * from example

Detection of Error After Database Execution

Now, when Server detects for corruption errors, it will show users an error MSG no-824

There could be several reasons for this error message. Users must know about causes of page level errors because when they have idea about root cause of problem, then only they can try to fix them. Therefore, following are the causes may responsible for that error message.

Note:SQL Server Backup Detected Corruption In the Database Log

Causes Behind Error Message 824

Hardware Level Causes

• Failure of certain hardware
• Hard Disk function failure
• Improper network establishment

Software Level Causes

• Malware intrusion
• File size exceeds its limit

Due to above mentioned circumstances erroneous messages arises at while user is working on SQL Server database and results distortion

Solution For SQL Server Page Level Corruption

Now point comes that how to resolve this error message. For solving this error, there are some manual methods, which are in-built commands of Microsoft and other are third party tools. Here we are describing both methods, now let’s discuss about both techniques.

Manual Solutions to Fix Page Level Corruption

In this section we will show you how to use built-in command to fix SQL Server page level corruption.

Repair Page Level Corruption Using DBBC CHECKDB Command

DBBC CHECKDB command is mostly used and well-known command to fix page level corruption. This built-in repair utility operates systematic data integrity and identifies areas of concern. This command has three options of recovery-REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST and REPAIR_REBUILD. Here REPAIR_ALLOW_DATA_LOSS is most risky command to use.

Note: Use REPAIR_ALLOW_DATA_LOSS command only at last resort because the command recover either whole data or loss all data. Therefore, it is highly recommended this at last.

Step-1

Check suspect pages of table presented in database, in which error 824 is showing on.

Step-2

Now check the state of SQL server database consistency by using DBBC CHECKDB command.

Step-3

Use PAGE_VERIFY CHECKSUM option, if page is not activated already.

Step-4

Stop all operations of SQL server. After that Copy corrupted Server database and Save it into a new memory allocation.

Step-5

Open SQL Server again and Create a blank SQL database with same name.

Step-6

Now, separate this new database from its original database.

Step-7

Execute following query to perform operation

EXEC sp_resetstatus ‘Test’ ;
ALTER DATABASE db_name SET EMERGENCY
DBCC CHECKDB(‘Test‘)
ALTER DATABASE test SET SINGLE_USER Mode
With ROLLBACK IMMEDIATE DBCC CHECKDB (‘Test’ , REPAIR_ALLOW_ DATA_LOSS)
ALTER DATABASE ‘Test’ SET MULTI_USER

If user execute above query then, maybe there are no anomalies with the data. The data is successfully recovered now, by following above steps.

Repair Page level corruption by Restoring From Full Backup

If user have maintained the recent backup of the corrupted database then they can restore the database from the backup file. If you don’t have any recent backup of data then you cannot recover their database via this method.

Step-1

Keep database Online and execute following query for restoring data from backup.

RESTORE DATABASE Test FROM DISK = 'D:\Backups\test.bak' ;

These statements will succeed without any Input /Output errors and user will get all his data recovered without loosing of any information.

Recover Page Level Corruption via SQL Recovery Software

Restoring database from backup is a good option only when, if you have already taken a recent backup of your database. Now, when manual methods are not able to recover user’s data then one can go with SQL Recovery Software, which is an alternate option to recover all data from a corrupted database file of SQL Server without any hassle.

Summary

In this blog, we discussed about how to recover corrupted data of MDF files by various methods. Manual methods have possibilities of data recovery and also could make a permanent data loss, we can’t apply those methods without any recent backup. Therefore, for recovering all data back, users can also try Third party application. It is an option to fix database from corruption.