I have two nodes with MSSQL2012 which work with AlwaysOn High Availability

After some crash I had to remove db from availability group on the one of the nodes. For restoration I've made backup (full+log) of db on the second node and restored it on the crashed one according to standard procedure. Seemed totally ok, but I've faced that when I give primary role to restored node then the application service (which uses Service Broker) doesn't do anything.

sys.databases said that for my db is_broker_enabled=1, but I've found several articles about broker on restored db must explicitly be enabled.

SET ENABLE_BROKER failed due to AlwaysOn.

Internet says that the only way to activate service broker is to restore db again with ENABLE_BROKER option. But for adding db to AlwaysOn I must restore it with NORECOVERY.

Trying to run

FROM  DISK = N'D:\111.bak'

I've seen notification about conflict between these two WITH options

Does anybody know correct way to make such restore? db is over 150GB and it always takes over two hours to check each versions:)

thanks in advance

Best Answer

Yes, using ENABLE_BROKER in the RESTORE conflicts with NORECOVERY. If your database is in NORECOVERY you'll need to wait until it is recovered before you renable the broker. If you are having trouble, try renabling with rollback immediate:

ALTER DATABASE db_name SET ENABLE_BROKER with rollback immediate