Error When Setting Up Database Mirroring

I spent a full day attempting to setup Database Mirroring on two SQL Server 2017 Development Editions with SSMS 17.4 to test a setup. Took a full day when it should have only took a few minutes and I could not find any help online, so I thought I would share my fix.

The Problem

I was getting this error at first:

Database "AdventureWorks" is not configured for database mirroring. (Microsoft SQL Server, Error: 1416)

After doing some research, I discovered that I needed to restore a full backup with the NORECOVERY option. Did that. Here is what I got then:

Database 'AdventureWorks' cannot be opened. It is in the middle of a restore. (.Net SqlClient Data Provider)

Well of course it is.. That is what I am suppose to do.. Everything I found online said make sure you restore it with NORECOVERY. I was in a never ending loop. I tried everything I could think of.

The Fix

The fix was easy. Too easy actually, but it took me about 8 hours to figure it out - use a old version of SSMS. Yes, its a bug in SSMS 17.x and from what I read 16.x as well. I had an older server with SSMS 12.x and I setup the restore in about 2 minutes.

Thanks for the 8 hours wasted due to your bug Microsoft.. Thanks a lot... If you want to help me make Microsoft aware of this issue, then join in on this UserVoice post.

Let me know if this helped you as well!

 

---

Originally posted at https://moreabout.tech/error-when-setting-up-database-mirroring/

---