0

I have service broker running on different servers.

Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there.

For the next time I need to rebuild a server that has service broker running in any of its databases

I would like to script all the service broker architecture for that specific database

-- Message Types
SELECT * 
FROM sys.service_message_types;

enter image description here

Basically using the scripts below, how can I be able to script all the service broker elements for a particular database?

-- Message Types
SELECT * 
FROM sys.service_message_types;

-- Contracts
SELECT * 
FROM sys.service_contracts;

-- Queues
SELECT * 
FROM sys.service_queues;

-- Services
SELECT * 
FROM sys.services;

-- routes
SELECT * 
FROM sys.routes;


-- Endpoints
SELECT * 
FROM sys.endpoints;

enter image description here

1

After seeing a recent comment from David Browne:

SSDT / SqlPackage supports Service Broker objects.

I tried it out. I created some service broker objects based on the demo here:

Then I download the DAC framework from here:

Then I ran this command (without the carriage returns):

sqlpackage 
  /Action:extract 
  /SourceServerName:.\instancename
  /SourceDatabaseName:AsyncProcessingDemo 
  /TargetFile:C:\scripts\sb.dacpac

Then I went to c:\scripts, right-clicked the dacpac file, and chose Unpack...:

enter image description here

This asked me for an output folder:

enter image description here

I clicked Unpack, went to that folder, and there were four files, including model.xml:

enter image description here

And sure enough, that .sql file contained a script for all the broker objects, assembled in almost the right order. It generated a script with the contract created before the message types it depends on. So you will end up with:

CREATE CONTRACT ... ([MessageType1] SENT BY INITIATOR, ...
GO
CREATE MESSAGE TYPE [MessageType1]...

You'll need to flip those around so the message types are created first.

I'm sure SqlPackage.exe and/or the DAC wizard has ways to restrict this to just the service broker objects, but this should be a good start.

  • I've downloaded the file, but I could not run the program. what is the exec called? I could not find sqlpackage – marcello miorelli Nov 13 '18 at 17:37
  • 1
    @marcellomiorelli If you've run DacFramework.msi, you should be able to find SqlPackage.exe in C:\Program Files\Microsoft SQL Server\150\DAC\bin. – Aaron Bertrand Nov 13 '18 at 17:41
1

Following on the accepted answer by Aaron Bertrand

and then the comments:

If you've run DacFramework.msi, you should be able to find SqlPackage.exe in C:\Program Files\Microsoft SQL Server\150\DAC\bin. – Aaron Bertrand

I run the following command from the location described above (without the carriage returns):

sqlpackage /Action:extract /SourceServerName:SQLPROD1 /SourceDatabaseName:COLA /TargetFile:"D:\Service Broker\COLA.dacpac"

and I got this result: enter image description here

after that, opening the generated file, all database objects were scripted, including the service broker ones.

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.