Enabling broker after Restoring Sql Server DataBase

Question

Hello, I have DataBase with enabled Service Broker. Then I want to restore my database in program from backup of other database, but after restoring(I restore on existing database name), my method, whitch enables Service Broker, puts this error:

    Msg 9772, Level 16, State 1, Line 1
The Service Broker in database "ServeDB2" 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.

This is my method:

public void TurnOnBroker()
{
    if (!this.database.BrokerEnabled)
    {
        this.server.KillAllProcesses(this.database.Name);
        this.database.BrokerEnabled = true;
        this.database.Alter();
        RefreshConnection();
    }
}

What should i fix here?Any suggestions?

Answer 1

Every database has a unique ID used by Service Broker. This ID must be unique across all databases in a Sql Server instance (well, it should be unique globally, but Sql Server doesn't have a way to enforce that). When you restore a database, you have an option of disabling Service Broker in the restored database, enabling it with the GUID of the backed up database (so that it can take over message processing from the backed up database) or assign it a new GUID. You're trying to do the second option while you still have the old database around and you run into GUID conflict.

See here for more info.

Answer 2

I found a very simple solution for that- just simlpy assign new service broker, like this:

public void TurnOnBroker()
    {
        if (!this.database.BrokerEnabled)
        {
            this.server.KillAllProcesses(this.database.Name);

            string brokerCommand = String.Format("ALTER DATABASE {0} SET NEW_BROKER", this.database.Name);
            this.database.ExecuteNonQuery(brokerCommand);

            RefreshConnection();
        }
    }