Service Broker GUID does not match the one in sys.databases

Sometimes you can get a problem when trying to configure Service Broker on a database that has been restored / copied from another source (e.g. to a DR server), or even when trying to enable Database Mail on an msdb database that has been copied from elsewhere.

The error is something along the lines of:

Alter failed for database 'xxx'

Cannot enable the Service Broker in database "xxx" because the Service Broker GUID in the database (guid number) does not match the one in sys.databases (different guid number)
ALTER DATABASE statement failed.

This is because the master database also keeps track of service broker for each database, so this is just saying the master database thinks service broker has last processed a different record to the the one the database believes.

In the situation above, where we know this is the case because the database has come from another source, the quickest fix is just to reset the service broker as follow:

ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE

This will drop and recreate all service broker settings and queues - so please only use this if you really do want to reset everything, don't do it if you have queues with statements in that you want processing - but for a quick fix on a DR database it can save a lot of headscratching.