5

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

Improve this question
1
  • You can enable via an ALTER Database command. This guy has a pretty good script in case you need a new broker vs enabling a broken one. davewentzel.com/content/…
    – Steve Mangiameli
    Commented Dec 9, 2015 at 20:23

1 Answer 1

Reset to default
2

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.