Script out Jobs in SQL Server


Do you want to script out the jobs available in SQL Server Agent ? There a many ways to skin the cat.



  • The probably easiest one is to us the built in functionality of SSMS. Simply select all the jobs (doing a STRG+A on the Object Explorer list) and choose Script.

ScriptSQLServerJobs



  • Script jobs separately (this can get tedious with having hundreds of Jobs :-))

ScriptSQLServerJobs_Simple



  • Use a simple SMO Application which can connect to the JobServer, query for jobs and write them down in a separate file. In addition it will create a calling .sql-Script file and a batch command which is parameterized to execute it against a new server. For geeks sake, I wrote this small Consoleapplication and attached it to this thread for your reference and further use.

image


If you have feedback on that or things that could be better, feel free to write me a feedback.


-Jens

JobScripter.zip

Comments (12)

  1. Adrian says:

    Good day, the JobScripter, can be run on sql server 2008? Thank you very much.

  2. No, the jobscripter scripts jobs for all SQL Server targets that are supported by the SMO 9.0 interface which is down to SQL Server 7.0.

    If you have any issues or fetaure requests with the scripter, feel free to drop me a mail.

    -Jens

  3. apple says:

    very nice Jens

    any chance you could post the source code here

    some firms don't allow third party scripts to run unless they are 'open source' code

  4. Larry Leonard says:

    Do you really expect anyone to run an EXE?  Post the source code, or quit wasting peoples' time and pull this post.

  5. Jens K. Süßmeyer says:

    Hi Larry,

    thanks for the nice and polite comment. I can hear people asking while posting the source "Why the heck do you post the source code, do you really want me to run Visual Studio and compile this thing ?" This is an easy to use .exe file, for you being ready to start right away after downloading it. I already send source code to a couple of people when they ask me for it (Normally people tend to behave polite and friendly sending in comments) Is there anything missing from the feaure that you want to see ?

    -Jens

  6. Satish says:

    The above technique saves my time. thank you very much for your valuable post as i got the advantage

  7. Vitahostage says:

    @Larry Leonard

    Better for people to think you're stupid than open your mouth and remove all doubt

    @Jens

    Big up, good work, much appreciated.

  8. Paul says:

    Thanks, was very helpful.

  9. Roger Berkelmans says:

    Hi Jens,

    Not long ago we migrated from SQL2005 to SQL2008R2 and I noticed that not all agent task are scripted at this time.

    Is there an easy way I can get all the jobs scripted?

  10. Jens K. Süßmeyer says:

    Hi Robert,

    do you mean the Script didn not pick up all jobs, or do you mean that in general youused a method that did not pick up all jobs. If the first, are these jobs sort of specific ? I do not use any filter at all in my script generation method, therefore I am curious if these jobs differ in any way. If the latter, which method did you use to generate the scripts ?

    -Jens

  11. A good little utility, for anyone interested, two lines of Powershell will achieve the same result:

    Import-Module “sqlps” -DisableNameChecking

    gci -path SQLSERVER:SQL<InstanceName>JobServerJobs | %{$_.script()} | out-file -filepath FileSystem::\SomeUNCPathAll` Jobs.sql