Msg 9772, Level 16, State 1 - The Service Broker in database "xxxx" cannot be enabled because there is already an enabled Service Broker with the same ID.

Question: I'm attempting to execute the following ALTER DATABASE statement to enable BROKER on a database we've just restored.

ALTER DATABASE [xxxxx] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

and getting this SQL error message

Msg 9772, Level 16, State 1, Line 5

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

How can I fix this problem and progress with enabling Service Broker? I've restored other databases on the same sql server without a problem . The database is restored successfully and connections are all OK

Answer:This error occurs when you are trying to restore the service broker enabled db to the original server , thereofre the databases have the same Broker identifier.
Every db has a unique identifier for Service Broker , which is essential for message routing - and you can explore this further through the service_broker_id in sys.databases.

To fix this issue - you'll need to run a preliminary ALTER DATABASE statement to set a new broker id.

USE [master]
GO
ALTER DATABASE [xxxxx] SET NEW_BROKER
GO

 
If you're moving multiple databases between different servers and the databases are utilizing Service Broker  - you'll need to be aware and make sure you add this preliminary step after you've restored but before enabling service broker for the sql server database.

Read More SQL Server Service Broker

Change the port for sql server service broker (SQL Server DBA)

Troubleshooting SQL Server Service Broker (SQL Server DBA)