Recovery of a Mirrored SQL Database
Recovering a mirrored database, whether it is to a SQL Server 2005 or SQL Server 2008 installation, requires that the existing mirror be broken. For simplicity, you may want to consider deleting both the Principal and Mirror databases and using the latest recovery point that DPM has in order to restore the data.
This blog post is geared towards a simple database mirroring recovery scenario and doesn’t discuss the replaying of transaction logs as part of the recovery process. As with any blog post, if there is additional information you would like to see provided or questions you would like to see answered, please pass those comments and questions along so that we can consider those for future posts.
This blog also includes a video demonstration of the recovery process involved in recovering a mirrored SQL database.
In our scenario, we will work under the premise that a bad transaction has been posted to the database and replicated without any means of backing it out cleanly. Now we have to restore the database to the servers and establish mirroring again. We will not consider the restoration complete until DPM is able to create a new recovery point after the restoration. This is a standard measure used to define when a recovery has been completed successfully.
Considering the scenario we have described, let’s begin.
In the screenshot below, we see that we have database corruption which needs to be recovered.
We begin by opening the properties of the Principal database and going to the Mirroring page. On the right side of the page is a button titled “Remove Mirroring”. Click on this button and confirm the selection by clicking on the “Yes” button in the following dialog to remove mirroring. After mirroring has been removed, you will be able to begin the restoration process.
If mirroring is left enabled, then DPM will fail on the restore with a detailed error message indicating that mirroring is still enabled and must be removed before the restore can be completed successfully.
With mirroring broken, delete both copies of the database on the Principal and Mirror servers. During the restore, you will decide which will be the Principal and which will be the Mirror based on how the restore to each server is performed.
With each database deleted, begin the recovery process by navigating to the Recovery tab in the DPM Admin console and selecting the recovery point you wish to restore. When the “Specify Database State” page of the Recovery Wizard appears, you will have the option here to choose whether this server will be the Principal or the Mirror.
In the screen shot below, you will note that the “Leave database operational” radio button has been selected which indicates that the server selected will become the Principal server.
Before you start the recovery to the Mirror server, you must make sure that you are recovering using the same recovery point that was used for the Principal server’s recovery. If not, you will receive errors when attempting to establish mirroring between the two copies of the database.
When the recovery has progressed far enough along that you can begin another recovery, you can start the Mirror database recovery. In the “Specify Database State” page of the Recovery Wizard, make sure that you choose the radio button for “Leave database non-operational but able to restore additional transaction logs”. This will restore the database with the “Restore with NoRecovery” option enabled. As you recall from the blog on establishing a database mirror, this is a requirement when seeding the Mirror server.
This radio button helps to minimize the number of steps involved in the recovery process.
Once both of the restores have been completed and the Principal and Mirror database servers have the same copy of the database restored, you should verify that the necessary data has been restored to the server. If the expected data is still missing, you may need to consider restoring from a different recovery point.
If there are additional transactions that need to be replayed, consider creating the mirror and then replaying the transactions so that SQL will replicate them on the fly to the mirror.
With the data having been restored to both servers, now it is time to run the “Database Mirroring Wizard” from within SQL Server on the Principal server and setup mirroring on the database. Once mirroring has been established, you have completed the portion of the data restore that your users are concerned about.
You have not completed the restore from a DPM perspective, however. There is still an additional step to consider.
Since the database was recovered to its original location, DPM will not be able to create any additional Recovery Points until a consistency check has been run on the database. Once this consistency check has completed successfully, a new recovery point will be created.
From the DPM perspective, this newly created recovery point confirms the successful completion of the mirrored database recovery.
Summary
In recovering a mirrored database, the mirror must be broken first. After the mirror has been broken, the same recovery point must be used when populating the Principal and Mirror servers.
After the restore has been completed and the data verified, the mirror can then be recreated. Before DPM can continue to protect the mirrored database, a consistency check will need to be run. Once complete, a new recovery point will have been created and DPM will be able to continue protecting the mirrored database moving forward.
Video
Vic Reavis
Support Escalation Engineer
Microsoft Enterprise Platforms Support