Showing posts with label restored. Show all posts
Showing posts with label restored. Show all posts

Wednesday, March 21, 2012

Msg 9772 -- Service Broker in database "" cannot be enabled

I backed up a database "Broker", and restored it on the same instance as "BrokerQA" (Broker db still exists, I need them both running on the same instance).

tried sending a few messages, sys.transmissions_queue says: The broker is disabled in the sender's database.

then tried to enable service broker for BrokerQA:

alter database BrokerQA set enable_broker

Msg 9772, Level 16, State 1, Line 2

The Service Broker in database "BrokerQA" cannot be enabled because there is already an enabled Service Broker with the same ID.

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.


How do I reset the service broker ID on the newly restored database?
Thanks!eh, not being lazy does wonders....

alter database viagogoQA set NEW_BROKER

does the trick...

Monday, March 12, 2012

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207

I restored a database to my test server from a production server. I ran "sp_change_users_login 'report' " and found 1 orphan id. Then I ran "sp_change_users_login 'auto_fix', 'orphan_id' ". It gets the error. I have run it before with success. Any idea why this time is different?

Once scenario where this may exist is if there is no login to fix the mappings with, then the procedure tries to create the login but you haven't specified a password. In that case, you have to use:

exec sp_change_users_login 'Auto_Fix', 'YourLogin', null, 'YourPassword'

I would guess that when it worked before, the login existed so it just remapped the database user to the existing login.

-Sue

|||

If you are using SQL Server 2005 SP2, you can also try ALTER USER ... WITH LOGIN syntax.

Please, let us know if you were able to solve the problem or if you have any additional questions .

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine