Home / server / Questions / 153124
Accepted
MGOwen
Asked: 2010-06-21 23:40:46 +0800 CST

When successfully configured SQL Server for database mirroring, got an error saying it's not configured for database mirroring

  • 772

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!

sql-server sql-server-2005 database-mirroring

2 Answers

  1. 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.

    • 4

  2. This worked for me a well:

    RESTORE DATABASE [MyDb]
FROM disk = 'C:\TEmp\MyDb'
WITH
REPLACE,
NOrecovery /* */
    • -1