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!