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 : all Agent Jobs Using SQL Server Management Studio (SSMS).aspx


SQL, agent, SSMS

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 :
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

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

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

Post by Hans Michiels on Thu 27 Oct 2016 08:20. Report Inappropriate Post
Website :
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 by Gangadhar on Tue 14 Mar 2017 16:53. Report Inappropriate Post

Does the below steps also export the schedule we have for each job (what time to run, which day to run etc)? I could not see that.

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.

How do we get the schedule script as well or should we create this for each job manually?
Post by Gangadhar on Wed 15 Mar 2017 00:16. Report Inappropriate Post

Never mind. I spotted it!!!
Post by Kaushal Rathore on Fri 17 Mar 2017 13:59. Report Inappropriate Post

Thanks a ton , this saved me few hours work
Post by Diego on Wed 19 Apr 2017 20:50. Report Inappropriate Post

T.H.A.N.K you V.E.R.Y M.U.C.H
Post by Gus on Fri 12 May 2017 19:59. Report Inappropriate Post

Saved me a lot of time !!!!
Post by Uday on Mon 15 May 2017 12:15. Report Inappropriate Post

How to generate scripts and save it in seperate files for sql jobs
Post by Niks on Wed 19 Jul 2017 10:25. Report Inappropriate Post

Thanks Very much useful
Post by Sanket on Thu 27 Jul 2017 05:13. Report Inappropriate Post

Great Post bro...saved lot of my time...keep up the good work
Post by Nathan on Thu 17 Aug 2017 17:34. Report Inappropriate Post

This was really helpful. Do you have a way to automate this for a weekly (or similar interval) backup?
Post by Ajmal on Fri 25 Aug 2017 05:22. Report Inappropriate Post

Hi, Very Nice...
Am facing problem while create script contains few syntax mismatch
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE Dbname
exec cmn_assetmatupd_offline_sp

USE dbname_NEW
exec cmn_assetmatupd_offline_sp

', --------------------------------------------------------------- mismatch
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
Post by Fiaz Khan on Thu 12 Oct 2017 17:53. Report Inappropriate Post


Post a comment   No login required !

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