Restore a mirrored database

This guide was created with Microsoft SQL Server 2008 in mind, but should work for any version of SQL Server. It assumes the databases are in a synchronous mirroring configuration with no witness server and that you are using an account with Admin access to both servers and databases.

To fully restore the database you will need to perform a database restore on the Principle SQL server, followed by a recreation of the database mirror up to the Mirror SQL server. In this guide the Principle server is Principle01 and the Mirror server is Mirror01, the database is called Database9. It assumes you have taken a database backup and stored the backup file at F:\Database9\

The process is:

1. Log on to Principle01.

2. Open SQL Server Management Studio and connect to correct database instance.

3. Expand Databases, right-click on Database9 and go to Tasks > Detach… (This is in order to kill any user sessions, you could go through the process at Kill database processes, but a detach / attach will do the job faster).

4. Right-click Databases folder and select Tasks > Attach…

5. Run the following query against master:

ALTER DATABASE Database9 SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

6. Right-click on Database9 and go to Tasks > Restore > Database.

7. Select From device:, then Add and browse to F:\Database9. Select the database backup file and then hit OK and then OK again.

8. Tick the check box under Restore. Click Options (to the left) and ensure Overwrite the existing database (WITH REPLACE) is selected. Press OK.

9. Once the restore has completed you may wish to check it is functioning correctly and the database has been rolled back to the desired state.

You will then need to re-establish the mirror to Mirror01. To do so:

1. Log on to Mirror01.

2. Copy the database backup file that you restored in Step 7 (above) from Principle01 to C:\Temp\ (or any suitable location) on Mirror01.

3. Open SQL Server Management Studio and connect to Mirror01\Database9 Database instance.

4. Expand Databases, right-click on Database9 and select Delete. Prss OK to confirm.

5. Right-click on Databases and select New Database….

6. Enter Database name as Database9 and press OK.

7. Right-click on Database9 and go to Tasks > Restore > Database.

8. Select From device:, then Add and browse to C:\Temp\ (or alternative location set in Step 2). Select the database backup file, hit OK and then OK again.

9. Tick the check box under Restore. Click Options (to the left) and ensure Overwrite the existing database (WITH REPLACE) is selected. In the bottom under Recovery state ensure the option which includes the text (RESTORE WITH NORECOVERY) is selected. Press OK.

10. Log on to Principle01.

11. Open SQL Server Management Studio and connect to Principle01\Database9 Database instance.

12. Expand Databases, right-click on Database9 and go to Properties.

14. Select Mirroring on the left and then hit Configure Security.

15. No witness server instance > Next > Next.

16. Connect to Mirror01\Database9 with Windows Authentication > Finish > Finish.

17. When the Database Properties window appears, click Start Mirroring.

You can check that the mirroring is working by logging back on to Mirror01, the Database9 database should have (Mirror / Synchronized / Restoring…) to the right.

Posted in Guides Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *

*