While doing a recent server move, I came across the need to script all agent jobs and save them to a file so that if we needed to reference what was there after the move we could get at the agent jobs without having to restart the old SQL Server.
It made me realize that sometimes what you want to do is available in SQL Server Management Studio, but that perhaps it is not always entirely obvious.
In the example below, where I have four jobs (imagine if it was 150 jobs), I want to script all of them out and save them for future use.
I could just right click on each one, choose “Script Job As, CREATE To, New Query Window”, which wouldn’t be that bad for 4 jobs, but assume you need to do this for 150 jobs, it could take you a while.
But here is an easier way…
You and click on Jobs in the Object Explorer then Hit F7 to display the Object Explorer Details pane. There all your jobs will be listed. From here you can select individual jobs, and use Ctrl+click to select multiple jobs, or Ctrl+A to select them all. Once multiple have been selected, right click and choose “Script Job as, CREATE To, New Query Window”, and all the jobs will be scripted into a new window.
From there you can save it, move it to another server, or do whatever you need to do with that script.
I hope this helps you save a few minutes if you need to do the same thing with your SQL Server Agent Jobs.
It would be slick if there was a way to programmatically do this from TSQL, but I haven’t discovered how to do that yet.
-Steve Stedman
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Impressive.
Thank you!!!!