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.