Mayank (10/26/2010)

---

Normally to enable the Broker Service, you need to simply use : "ENABLE_BROKER"
But when you restore the back up of database db1 to database db2, and after that if you want to enable broker service on database db2, then what to do? - That was my question.

SanDroid (10/27/2010)

---

The wording of the question and the code was misleading and confusing.

The code created one backup file db1.bak, and then restored from a different file called main.bak. This means that it did not restore the backup created for db1 in the first script to to db2 in the second script. Your code restored a backup called main.bak. You quoted comment above does not apply since it assumes we all corrected a mistake in your questions code that was not apparent until after you had made responses here.

Did the database in the main.bak backup file have the Broker Service enabled when it was created?
Did you question make that fact clear?

It was completely unclear if any database had Broker Service enabled, disabled, or if it had ever been set-up when the backup file was created.

This could have been a really decent high level question had anyone taken time moment to read it and execute the code with it prior to posting.

Hardy21 (10/27/2010)

---

that's really good observation - I think most of us didn't check the backup file which author has used for restoration.

da-zero (10/28/2010)

---

[quote]
Well I did I just ignored because it was an obvious typo (and the answers didn't have any error messages)

Mayank P - Clarion, India (10/26/2010)

---

Yes Phil,

... you restore the back up of database db1 to database db2, and after that if you want to enable broker service on database db2...

Hugo Kornelis (10/26/2010)

---

Thanks for the question. I missed a point, but learned something new.

The reason I missed a point is because, when doing some research, I found this text:
"If you attach a database with the same service broker identifier as an existing database, SQL Server deactivates Service Broker message delivery in the database being attached."
(This is from http://msdn.microsoft.com/en-us/library/ms166057%28SQL.90%29.aspx).
The quote specifically mentions attaching, but since the entire page is about attach and restore, I assumed that the restore behaviour would be identical. Obviously not.