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

    Question

  • 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!
    Tuesday, November 29, 2005 3:18 PM

Answers

  • eh, not being lazy does wonders....

    alter database viagogoQA set NEW_BROKER

    does the trick...

    Tuesday, November 29, 2005 3:25 PM

All replies

  • eh, not being lazy does wonders....

    alter database viagogoQA set NEW_BROKER

    does the trick...

    Tuesday, November 29, 2005 3:25 PM
  •  Thats lovely..helped me to :)
    Monday, March 09, 2009 11:21 PM
  • Here's what I did.  I used dynamic sql for my situation but you do not need to.  I added the "rollback immediate" to close connections to the database, otherwise it may hang on the alter statement, waiting for any connections to close.

    DECLARE @SQL nvarchar(max)
    BEGIN TRY
        SET @SQL = 'ALTER DATABASE ' + db_name() + ' SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE '
        PRINT @SQL
        EXEC sp_executesql @SQL
    END TRY
    BEGIN CATCH
        SET @SQL = 'ALTER DATABASE ' + db_name() + ' SET NEW_BROKER WITH ROLLBACK IMMEDIATE '
        PRINT @SQL
        EXEC sp_executesql @SQL
    END CATCH
    GO
    • Proposed as answer by CasMeijer Tuesday, September 20, 2011 1:29 PM
    Tuesday, October 20, 2009 6:50 PM