By SuperG - Last updated: Tuesday, April 3, 2012 - Save & Share - Leave a Comment

Had a need to mirror a database the other day… As its been awhile since I have had to do this and it took entirely too long. After playing with it for a couple of days here are the steps we used to make it work.

Make a full backup of the database.

Make sure you know where the backup is going. (Path to file)

Next backup the transaction file. Again make sure you know where the files are.

On the mirror partner make a new database with the same name.

Restore the backup of the Primary Database.

Use the path to where you backed up the file or you can copy the file to the new server.

Under Options make sure you recover with “RESTORE WITH NORECOVERY”!!!!!!! Also you may need to change the path to the database files if they are different on this server.

After the database has been restored, you will see the database is “Restoring”

You now need to restore the Transaction Log.

Again point to where the file is either local copy or network.

Under Options make sure you recover with “RESTORE WITH NORECOVERY”!!!!!!!

Back on the Primary database, you can now select mirror to setup the mirroring

Click the “Configure Security” button

I am using a witness server

Setup Primary server and port#, I used the defaults

Do the same for the Mirror and Witness

Because I am using the same service account on the servers and I am in a domain environment, I do not need to use a service account. Leave blank.

After endpoints have been made start mirroring.

Status will show Synchronizing

And after the two databases are synchronized

On the Mirror copy you will see the database “In Recovery” this is normal


A couple of errors we ran into

Mirroring Error 1418 – For us this was due to having encryption enabled on one of the Endpoints and not on all the others.

Mirroring Error 1416 – For us this was due to NOT restoring the database in “RESTORE WITH NORECOVERY”. Make sure you use “RESTORE WITH NORECOVERY”

Posted in SQL • Tags: Top Of Page

Write a comment

You need to login to post comments!