1

I'm using SQL Server Management Studio to generate scripts for all the objects in an existing database:

enter image description here

but the objects related to SQL Broker (messages, queues and services) are not generated.

I've fiddled with the advanced options but still can't get them out. Is there a way to do it?

3
  • Is this SSMS 2008 or 2012?
    – JNK
    Apr 15 '13 at 20:34
  • @JNK Neither, that looks like 2005 to me. Giveaway is the lack of an Activity Monitor icon on the toolbar. But then, it may have been removed, but I doubt it.
    – RoKa
    Apr 15 '13 at 20:39
  • Sorry, I posted the wrong screenshot. It's SSMS 2008.
    – Farinha
    Apr 16 '13 at 8:08
5

Even in Management Studio that ships with SQL Server 2012 (BTW please stop using SSMS 2005 to manage 2008+ instances), the Generate Scripts option will not offer Service Broker objects, whether you select "script all" or select individual objects. You'll need to use other methods - SMO, PowerShell, or right-clicking within the Service Broker node of Object Explorer and scripting items individually. You can speed this up a bit using Object Explorer Details (in the right-hand pane you can select multiple objects and script them), but this is still one operation per object type.

PS this was complained about years ago, but sadly, closed as Won't Fix.

On the plus side, David is right below: SqlPackage.exe will definitely script out service broker objects (it just does it in the wrong order). See my answer to a more recent question here:

2
  • Bummer! Thanks for the clarification. I was trying to avoid manual scripting or getting yet another tool, but I guess that's what has to be done.
    – Farinha
    Apr 16 '13 at 8:10
  • 1
    SSDT / SqlPackage supports Service Broker objects. Nov 12 '18 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.