Fix: The Service Broker in database “<DBName>” cannot be enabled because there is already an enabled Service Broker with the same ID.

Scenario: You backup & restore (or) create a copy of a SQL DB enabled with SQL Service broker under same SQL server instance. Then run ‘ALTER DATABASE SET ENABLE_BROKER’,  you’ll receive the error as given below:   Msg 9772, Level 16, State 1, Line 1 The Service Broker in database "<DBName>" cannot be enabled because there is already an enabled Service Broker with the same ID. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.   Upon checking is_broker_enabled status in sys.databases for the reported DB, you’ll see the results for is_broker_enabled as zero.   Query: SELECT […]

Read more