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?
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' */