July 26, 2012 at 10:10 am
We have an instance of SQL 2008 R2 that is used to host databases for user training. We keep a set of backups of these databases that are restored every night or a few times a week, overwriting users' changes and returning the DBs to an inititial state.
The application being hosted required than Service Broker is enabled, but sometimes the Broker Enabled setting changes from true to false, which can cause connection string errors in the application.
I made a new set of backups to be used for refreshes yesterday, and I know Broker Enabled was set to true when I makde the backups. Yet when the refreshes happened last night, one of them reverted to false.
What can cause the Broker Enabled setting to change on it's own? Is there a default setting in SQL 2008 R2 I can change?
July 26, 2012 at 10:56 am
From BOL:
Specifies that Service Broker message delivery is enabled at the end of the restore so that messages can be sent immediately. By default Service Broker message delivery is disabled during a restore. The database retains the existing Service Broker identifier.
The fix is to pass with WITH BROKER_ENABLE option during the restore. In your case, you might consider passing the WITH NEW_BROKER instead sinc e you probably do not want to have multiple brokers with the same ID's and you don't want the training system to be attempting to pass the same messages that might be in the queue as of the backup.
For specifics on the restor options - check out this BOL entry:
http://msdn.microsoft.com/en-us/library/ms178615.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 26, 2012 at 12:25 pm
I will try that - thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply