Resolving Page-Level Corruption in MS SQL Databases

A page is the minimum unit of storage in MS SQL Server. Typically, it is 8 KB of data. Page level corruption in MS SQL Server occurs when the pages in SQL Server are damaged due to different reasons, such as virus attack, hardware problem, electricity outage, malicious software, etc. 

How to detect page-level corruption in MS SQL Databases?

The DBCC CHECK command is used to detect page corruption. This command checks the integrity of the database.

DBCC CHECKDB

A typical page-level corruption error may look like the following:

Msg 8928, Level 16, State 1, Line 1

Object ID 1234, index ID 2, partition ID 78901234, alloc unit ID 987654321 (type InRowData) has been corrupted. The page ID 567890123 may not be correct. There are 100 pages on the disk with this object ID. The PageAudit property is incorrect.

How to detect page-level corruption in MS SQL Databases using DBCC checktable?

The DBCC CHECKTABLE command can detect page-level corruption errors. This option is faster than the DBCC CHECKDB command because it only checks the integrity of a single table, instead of the entire database. The following example shows how to check the integrity of the table Person.Person.

DBCC CHECKTABLE (‘[Person].[Person]’)

How to fix page-level corruption in MS SQL Databases?

You can follow the below methods to fix page level corruption in SQL database.

Method 1: Restore from Backup

First, you can try to restore your database from the current backup. The following example shows how to create a backup of SQL Server database (for example stellardb) using T-SQL.

BACKUP DATABASE stellardb

TO DISK = ‘C:\backup\backup.bak’

You can restore the database using the following command:

RESTORE DATABASE stellardb

FROM DISK = ‘C:\backup\backup.bak’

WITH REPLACE;

The above command restores the database, named stellardb, using the backup stored in the C drive in the backup folder. If there is an existing database, the WITH REPLACE option will replace the existing corrupted database with the new database.

Restore the Damaged Page 

Restoring the entire database using backup can take a lot of time, specifically if it is a big database. There is an option to restore just the damaged page. This way, you do not need to restore the entire database. Here’s how.

• In the SQL Server Management Studio, go to the Object Explorer and right-click the database, select Tasks > Restore > Page.

• You need specify the file id and the page id. You can get the page id from the error message when you run the DBCC CHECKDB command. To get the file id, use this query:

SELECT name as FileName, file_id AS FileID

FROM sys.database_files;

• Press the Add button, enter the File ID and the Page ID, and press OK.

Method 3: Use a Third-Party MS SQL Repair Tool

Another option is to use a third-party SQL repair tool, such as Stellar Repair for MS SQL. This software can be used to repair database with page-level corruption. The software can repair any page-level problem and restore the database. It can also export the data to other file formats, like Excel, CSV, and HTML.

Using this software is simple. Click the Find button to find the data file. A data file in SQL Server is the file that contains the data in the SQL Server database.

The Find button can help you find the location of the file. Once found, use the following T-SQL command to set the database offline.

ALTER DATABASE stellardb SET OFFLINE;

Once offline, go to the Stellar Repair for MS SQL and press the Repair button.

Once the database is repaired, you can save the information in a New Database or in a Live Database.

Conclusion

In this article, we have discussed page-level corruption and how to detect corruption using the DBCC CHECKDB and DBCC CHECKTABLE commands. We have also mentioned different methods to fix the page level corruption in the database. You can restore the entire database using a backup or just restore the damaged page. Recovering from backup is the most common option but restoring the page can be a faster option if just one page is corrupt. We have also mentioned a third-party tool, called Stellar Repair for MS SQL. It is a great option if the other methods fail.