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:

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



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.


This worked for me a well:

FROM disk = 'C:\TEmp\MyDb'
NOrecovery /* */

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.