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;
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;
Best Answer
After seeing a recent comment from David Browne:
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):
Then I went to
c:\scripts
, right-clicked the dacpac file, and chose Unpack...:This asked me for an output folder:
I clicked Unpack, went to that folder, and there were four files, including
model.xml
: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:
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.