Scripting all Agent Jobs Using SQL Server Management Studio

Category : Blogs Published : April 2, 2013 User Rating : 4.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


Related Articles

The following articles may also be of interest :

Link back to this article : https://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 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
Go
exec cmn_assetmatupd_offline_sp
GO

USE dbname_NEW
Go
exec cmn_assetmatupd_offline_sp
GO

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

Thanks!
Post by Iman Liu on Tue 24 Oct 2017 16:19. Report Inappropriate Post

Thank you very much!
Post by Ben on Wed 20 Dec 2017 08:43. Report Inappropriate Post

Thanks a lot!
Post by Paul on Thu 25 Jan 2018 05:46. Report Inappropriate Post

Thanks
Post by h on Mon 29 Jan 2018 15:51. Report Inappropriate Post

thanks
Post by Champika on Wed 14 Feb 2018 12:22. Report Inappropriate Post

Tnks LOT
Post by joanna on Tue 20 Feb 2018 10:27. Report Inappropriate Post

perfect
Post by Eman on Tue 08 May 2018 18:45. Report Inappropriate Post

Can we manually start jobs simultaneously in SSMS? Need to rerun filtered jobs at the same time.
Post by Sunil Rohilla on Fri 18 May 2018 08:22. Report Inappropriate Post

Perfect. time saving artical
Post by Daniel on Tue 03 Jul 2018 09:06. Report Inappropriate Post

What a good tip!
Post by Hong on Thu 26 Jul 2018 10:23. Report Inappropriate Post

Saved my time. Thank you very much!
Post by Anshul on Fri 03 Aug 2018 11:59. Report Inappropriate Post

Awesome approach. saved valuable time
Post by anonimo on Tue 18 Sep 2018 18:09. Report Inappropriate Post

gracias!
Post by Matt P on Wed 31 Oct 2018 15:31. Report Inappropriate Post

Saved me about two hours of work. Dig it, man !!
Also helpful to do e.g. mass delete for database (use with caution) when pressing F7 on database node in SSMS
or scripting out logins.
Doing SQLServer support for more than 10 years now, but this F7 / Object explorer trick I never had found ...
Post by Jaak Leen on Tue 27 Nov 2018 13:55. Report Inappropriate Post

Thanks a lot
Post by DJ on Wed 06 Feb 2019 20:43. Report Inappropriate Post

This saved me so much time. Great post. Thank you!
Post by Scot Kelly on Mon 22 Apr 2019 22:58. Report Inappropriate Post

Your legacy of awesomeness continues!
Post by irshad md on Thu 27 Jun 2019 08:15. Report Inappropriate Post
Website : https://www.tapresume.co.in/
Nice info...
Post by Mohan on Fri 28 Jun 2019 06:19. Report Inappropriate Post

Time saving Information ......
Post by Link on Fri 12 Jul 2019 17:37. Report Inappropriate Post

Excellent, thank you for sharing this simple option.
Post by sabeer on Tue 06 Aug 2019 07:28. Report Inappropriate Post

Thanks a lot !!!. It saves me more time.
Post by Vanesca on Tue 20 Aug 2019 20:23. Report Inappropriate Post

Excelente!!! Valeu!!!
Post by Sanchita on Wed 25 Sep 2019 23:21. Report Inappropriate Post

Can you help me to get job script in tabular format by sql query
Basically my requirement is read the job script from dot net code, i'm looking for any query regarding the same
Post by Sanchita on Thu 26 Sep 2019 07:51. Report Inappropriate Post

Is their any way to get job script by sql query (e.g. using sp_helptext ___ we can read script of sql store procedure)
Post by Mohammad Atiqur Rahman on Sun 27 Sep 2020 10:32. Report Inappropriate Post

Thanks a lot !!!. It saves me more time.
Post by Paul Thomas Silvey on Thu 08 Oct 2020 19:05. Report Inappropriate Post

didn't script the schedules in SQL 2019. One step forward Microsoft ... 10 back.
Post by Deep on Tue 05 Jan 2021 09:19. Report Inappropriate Post

Great job. Really a time saver.
Post by Derek Wallace on Fri 29 Jan 2021 22:06. Report Inappropriate Post

Ah mighty. With over 4000 SQL Server instances and growing weekly this tip has save me and my team a heap of time. Many Thanks, Derek
Post by Dharmendra Kumar Yadav on Mon 30 Aug 2021 12:55. Report Inappropriate Post
Website : https://sqlmatters.com
Thanks for save my time
Post by Jesse on Tue 08 Mar 2022 11:09. Report Inappropriate Post

Thank you for turning the sync job from 1 hour to 10 minutes !!!!!
Post by PRM on Fri 30 Sep 2022 10:20. Report Inappropriate Post

Fabulous.. saved lots of time.. thank you!
Post by Jo on Fri 06 Jan 2023 07:36. Report Inappropriate Post

F7
Ctrl + A
Create scripts
Cooool....!!!

Thank you :)
Post by Ellioth on Fri 24 Nov 2023 00:33. Report Inappropriate Post

amazing! many thanks!
Post by Maurice on Sat 02 Dec 2023 10:11. Report Inappropriate Post

Awesome
Post by Grant on Thu 11 Jan 2024 19:37. Report Inappropriate Post

That was easy... thanks! 10+ years after the original post, still helping!
Post by Jennifer on Wed 24 Jan 2024 16:29. Report Inappropriate Post

This is great. Thank you so much! Can't believe it still works. :)
Post by Sunil on Wed 21 Feb 2024 15:15. Report Inappropriate Post

This is great stuff and so much useful. Thanks SQLMatters for all the support.

Post a comment   No login required !

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