Sql-server – When successfully configured SQL Server for database mirroring, got an error saying it’s not configured for database mirroring

database-mirroringsql serversql-server-2005

I need to replace the old database in our test environment with a fresh copy of the production database.

The test environment is actually comprised of two instances of the test database, on 2 different servers, in a mirroring configuration (it's got mirroring because production has mirroring, and the client wants the test environment to be just like production).

I thought this would be a simple matter of:

  • Getting a backup (the .bak file) of the production DB
  • Temporarily disabling the mirroring for the test DBs
  • Overwrite (i.e.: restore) them from the (prod) backup
  • Re-activate mirroring

But apparently this isn't nearly enough hoops to jump through. After wrestling with it for a day (permissions problems, cryptic errors, problems that go away if you restart SSMS (seriously), I'll spare you the full list…) I finally got the new DB restored onto both the principle and mirror instances of the test DB.

However, somewhere along the line SQL Server abandoned the mirroring configuration settings, so I right clicked on the principle, chose mirroring, and went right through the security wizard. When I clicked "Finish" at the very end of the security wizard, it did it's thing and I got the screen that reports that everything was set up successfully ("Configuring endpoints" – "Success"). Immediately after that is another window asking if I wish to start mirroring, however, clicking start on that window, I get the following SQL Server error:

An error occurred while starting mirroring.

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

So I'm getting "Mirroring is configured successfully!" Then, as part of the same process, "mirroring can't start because you didn't configure it!" šŸ™

Any ideas? Anyone seen this before, or know how to tease out a more useful error message or other info?

Update: Solved
As Brian says below, I'd restored the mirror database with the wrong recovery option, the correct way was:

RESTORE DATABASE [MyDb]
FROM disk = 'C:\TEmp\MyDb_LIVE_Prod_backup_2010-06-18_for_test_server.bak'
WITH
REPLACE,
NOrecovery /* This should be 'norecovery', my problem was I used 'recovery' */

Thanks!

Best Answer

Error 1416 often occurs when the mirror database has been recovered. Ensure that the mirrored database is in a Restoring state (NORECOVERY option)

Run the query below on the mirror server and verify the state of the database.

select database_id, user_access_desc, state_desc, recovery_model_desc, is_broker_enabled, log_reuse_wait_desc from sys.databases where name = 'database'

If the mirror database is in the ONLINE state, you will need to restore the database and the subsequent T-Log backups again with NORECOVERY option before setting the mirroring partners.

Related Topic