How to Diagnose and Fix Database Corruption in SQL Server Mirroring Environments?

Database mirroring is a feature in SQL Server for disaster recovery. In this, two copies of the database are maintained on different instances of SQL Server - a principal server and a mirror server. Optionally, a witness server is used to synchronize the data between the principal server and the mirror server.

Sometimes, you face issues with the database in the mirroring environment. If the principal server database fails, the mirror server can be used.

However, if the principal database is corrupt, you may get error messages similar to the below:

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 123490, index ID 1. Page (1:1234) has invalid header values. The PageId in the page header = (0:0).

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 98721, index ID 0. Page (3:5678) is missing a reference from previous page (2:3456). Possible chain linkage corruption.

Here, 'Object ID' is the ID of the table.

To know the name of the table using the ID, you can run the following query:

DECLARE @object_id INT = 123490

SELECT OBJECT_NAME(@object_id) AS ObjectName

Here, the Object ID is 123490.

Reasons for Corruption in Database in Mirroring Environment

A database can get corrupted in SQL Server mirroring environment due to several reasons, such as:

Hardware problems in the principal or the mirror server.
Virus or malware attack affecting the principal, witness, or mirror server.
Some of the servers used in the database mirroring of SQL Server are damaged due to natural disasters.

How to Diagnose Corruption in Database in SQL Server Mirroring Environment?

You can run the DBCC CHECKDB command as given below to detect corruption errors in the database.

DBCC CHECKDB('stellardb')

The above DBCC CHECKDB command will detect corruption errors in the database named stellardb.

In addition, you can see the ErrorLog in SQL Server. For this, open the SQL Server Management Studio (SSMS) and go to Management > SQL Server Logs.

How to Repair and Recover Corrupt Database in Mirroring Environment?

In case the principal database gets corrupted, database mirroring has a function that helps replace the corrupt pages with the healthy copy from the partner database. For example, if the principal server has some corruption errors, the database mirroring will detect the pages with corruption and copy the healthy pages from the mirror database to fix the problem automatically.

Here are some errors that are fixed automatically in the database mirroring environment in SQL Server:

Error Code	Error Message
823	Error caused by a failed cyclic redundancy check (CRC) in the operating system.
824	SQL Server detected a logical consistency-based I/O error.
829	A page has been marked as 'restore pending'.

Note: If a database fails in the database mirroring environment, the mirror server will not change to the principal server. The other server will be the mirror.

If the synchronization does not fix the problem, you need to restore the database from the last good backup. To do so, you can use the RESTORE command in T-SQL or the user interface in SSMS.

Once the database is restored, open the SSMS, select the mirror database, right-click on it, and select the Properties. On the Mirror page, press the Failover button. This will change the mirror database to the principal database.

If you don't have an updated or healthy backup, you can try to repair the corrupted database using the DBCC CHECKDB command (see the below example).

USE master
GO

ALTER DATABASE stellardb SET SINGLE_USER
GO

DBCC CHECKDB (N'stellardb') WITH REPAIR_REBUILD
GO

ALTER DATABASE stellardb SET MULTI_USER
GO

What if nothing works?

If you do not have a backup and the DBCC CHECKDB fails, then the only option is to use a third-party SQL database repair software, such as Stellar Repair for MS SQL. This software can repair the corrupt SQL Server databases and recover all the data. It can repair both MDF and NDF data files.

The software can restore selected objects or all the objects from the database. It also shows preview of all the recoverable data. You can restore the database to a new database file or a live database. It also allows to save recovered the data to Excel, CSV, or HTML file.

Conclusion

Database mirroring feature in SQL Server helps fix various corruption errors. This feature replaces the corrupt pages with the healthy pages from the mirror database. In this article, we have explained the basic concepts about database mirroring. We have also mentioned how to diagnose and repair corrupt database in SQL Server mirroring environment. If native solutions don't work, you can use Stellar Repair for MS SQL - a software specialized in repairing corrupted SQL Server databases.