Setting up Service broker after restoring a database.

With a

Service Broker

application, if you have a Test environment where Production backups are restored, it often becomes necessary to reconfigure the service broker after restoration.I first look at the msdb sys.routes table to get the broker_instance.

SELECT name, remote_service_name, broker_instance
FROM msdb.sys.routes

I compare the value of the broker_instance with the service_broker_guid of the DB restored.
SELECT name, database_id, service_broker_guid
FROM sys.databases
WHERE is_broker_enabled = 1

If it does not match, use the ALTER ROUTE command to change the instance value. In case, you rename the older DB, you will end up with two databases having the same broker_instance. In such a case, you can change the GUID associated with the old database

ALTER DATABASE DB_OLD SET NEW_BROKER WITH ROLLBACK IMMEDIATE

and disable the broker for that DB.

Finally, for the restored DB, change the route to point to the appropriate server.
ALTER ROUTE TargetService
WITH
ADDRESS = 'tcp://address:port'