SQL Server – Enable Service Broker When Deploying Solution

I wanted to add that the database is a restore from a SQL Server 2005

When you restore a database the Service Broker in it gets disabled and must be explicitly enabled (there are reasons why this is necessary, but I won't go into them). If you restored the DB and then set up the AG w/o enabling broker first, you have set up the AG w/o the Service Broker enabled and now you cannot run the ALTER DATABASE SET ENABLE_BROKER. Unfortunately to set it up correctly you must enable broker before adding it to the AG, which means you must tear apart the AG, enable broker, then set set up the AG again.

There are only 4 steps to enable service broker for a Database and non of them use logical file name as parameter

Try this

USE MASTER
GO

IF EXISTS ( SELECT  is_broker_enabled, Service_Broker_Guid,*
            FROM    sys.databases
            WHERE   name = 'DB2'
                    AND is_broker_enabled = 0 ) 
BEGIN                    

    -- Step 1 ---  Rollback running transactions and brings the database in a single user mode.
    ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE


    -- Step 2 --- To avoid distributed conversation and OLD database GUID conflicts.
    ALTER DATABASE DB2 SET NEW_BROKER;


    -- Step 3 --- Set the is_broker_enabled flag to true  
    ALTER DATABASE DB2 SET ENABLE_BROKER;


    -- Step 4 --- Brings the database back to Multi user mode and allow shared locks\connections.
    ALTER DATABASE DB2 SET MULTI_USER WITH ROLLBACK IMMEDIATE

END