Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Worked! Thanks. –  MGOwen Jun 23 '10 at 4:29
add comment

This worked for me a well:

RESTORE DATABASE [MyDb]
FROM disk = 'C:\TEmp\MyDb'
WITH
REPLACE,
NOrecovery /* */
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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