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
RESTORE DATABASE [dbname]
FROM DISK = N'D:\111.bak'
WITH NORECOVERY, ENABLE_BROKER
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: