Scripting all Agent Jobs Using SQL Server Management Studio

Category : Blogs Published : April 2, 2013 User Rating : 5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




I recently came across a way to script up all agent jobs in one go within SQL Server Management Studio (SSMS). Previously I used to right click on each job and select ‘Script Job as’ to script up each one individually, but this can be very labourious if there are a lot of jobs to do.

To script all jobs, just open the ‘Object Explorer Details’ from the View menu in SSMS, or press the F7 key. Click on any job in the Object Explorer window and a list of all the agent jobs appears in the ‘Object Explorer Details’ window.

Select all the jobs you want to script (press the Ctrl button while clicking to select individual jobs) and then right click and select the scripting option you want. This will then create all the selected jobs as a single query. You can script to a query window, file etc. Scripting all SQL Agent Jobs

Link back to this article : http://www.sqlmatters.com/Articles/Scripting all Agent Jobs Using SQL Server Management Studio (SSMS).aspx

Keywords

SQL, agent, SSMS


Comments
Post by Jaroslaw on Thu 24 Oct 2013 15:49. Report Inappropriate Post

thank you. great stuff!
Post by Luke Embree on Fri 14 Feb 2014 16:44. Report Inappropriate Post

This saved me a lot of time. Thanks!
Post by TXP-7 on Mon 14 Jul 2014 11:38. Report Inappropriate Post

thanks man. really helpful
Post by Tracy on Fri 15 Aug 2014 14:05. Report Inappropriate Post

Thanks!!!!! Another happy customer.
Post by chander on Fri 22 Aug 2014 03:50. Report Inappropriate Post

Do you have any script which will backup all these jobs to a file
Post by Azeez on Fri 05 Sep 2014 11:00. Report Inappropriate Post

Good One
Post by Jannie on Sat 13 Sep 2014 19:02. Report Inappropriate Post
Website : http://www.knowinfo.co.za
Thanks!
Post by Jason on Mon 15 Sep 2014 10:43. Report Inappropriate Post

What a time-saver. Thanks!
Post by OKC on Mon 03 Nov 2014 14:36. Report Inappropriate Post

Concur with all the previous posts! Big time saver with regards to migrating jobs from one server to another.
Post by Gilda on Tue 11 Nov 2014 22:29. Report Inappropriate Post

wooohooooo!!!!!!!!!!!!!!
Post by Deacon on Thu 04 Dec 2014 05:54. Report Inappropriate Post

thank you so much!!!
Post by Ian on Thu 18 Dec 2014 23:59. Report Inappropriate Post

Never knew.... Thanks!!!
Post by Nishad on Tue 13 Jan 2015 23:40. Report Inappropriate Post

Thanks a lot! Very useful tip!
Post by Dips on Mon 13 Apr 2015 08:39. Report Inappropriate Post

Thanks a lot!
Post by Gerald Britton on Mon 27 Apr 2015 19:42. Report Inappropriate Post

I hold cvtrl and click on job 1, fine. I keep holding ctrl and hit job 2 but then job 1 is deselected!

So, I can only select one at a time :-( How did the rest of you do it?
Post by Puneeth on Tue 28 Apr 2015 20:10. Report Inappropriate Post

Awesome
Post by Yvan Rochon on Tue 05 May 2015 13:05. Report Inappropriate Post

Reply to Gerald Britton

You have to press the F7 key so the Object Explorer Details window appears (the one on the right of the above screen picture). It is on that window you can select more than one job to script. On the Object Explorer window you can only select one at a time like it happenened to you and me (at first). Probably too late for you but hope it can help somebody else.
Post by DavidBird on Tue 11 Aug 2015 10:17. Report Inappropriate Post

Great Stuff
Post by Piotr L on Fri 04 Sep 2015 08:46. Report Inappropriate Post

So simple! Thanks a million.
Post by Re on Thu 17 Sep 2015 10:43. Report Inappropriate Post

Great help! Thank you!!!
Post by desperadomar on Sun 25 Oct 2015 14:03. Report Inappropriate Post

Ahh great , you saved a few hours
Post by Latha on Mon 28 Dec 2015 07:00. Report Inappropriate Post

awesome info.. Thanks a lot...
Post by mikhail on Mon 25 Jan 2016 03:37. Report Inappropriate Post

Amazingly useful! Thanks a ton.
Post by ece on Thu 03 Mar 2016 10:02. Report Inappropriate Post

Many thanks. :-)
This is a great help.
Post by Marina on Thu 24 Mar 2016 10:09. Report Inappropriate Post

Geeat:) Very helpfull.
Post by Bala on Fri 06 May 2016 21:50. Report Inappropriate Post

Very helpful, thanks!!
Post by BrandyDBA on Fri 09 Sep 2016 16:08. Report Inappropriate Post

Excellent! Thank you so much for this
Post by Gerald Britton on Fri 09 Sep 2016 16:51. Report Inappropriate Post

great!
Post by Hans Michiels on Thu 27 Oct 2016 08:20. Report Inappropriate Post
Website : http://www.hansmichiels.com
Great tip, thanks! A time saver!
Post by Ad on Fri 04 Nov 2016 05:49. Report Inappropriate Post

Brilliant in its simplicity! Tank you.
Post by Jerry on Tue 20 Dec 2016 14:36. Report Inappropriate Post

Great contribution. Thanks !!!
Post by Mulumebet on Sun 01 Jan 2017 08:43. Report Inappropriate Post

How do you script the 2012 Version? the GUI has different dropdown, .
Post by SQLMatters on Fri 20 Jan 2017 11:10. Report Inappropriate Post

Hi Mulumebet, thanks for your comment. I've tried this with Management Studio 2012 (and 2014) and it all works for me.
Post by Raghuram reddy on Fri 27 Jan 2017 08:53. Report Inappropriate Post

very helpful! thanks

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered