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/