Freigeben über

Script out Jobs in SQL Server

  • Artikel

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

  • Anonymous
    June 01, 2009
    PingBack from http://uniformstores.info/story.php?id=17031

  • Anonymous
    August 30, 2009
    Good day, the JobScripter, can be run on sql server 2008? Thank you very much.

  • Anonymous
    August 30, 2009
    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

  • Anonymous
    February 22, 2010
    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

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

  • Anonymous
    October 04, 2010
    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

  • Anonymous
    January 04, 2011
    The above technique saves my time. thank you very much for your valuable post as i got the advantage

  • Anonymous
    May 16, 2011
    @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.

  • Anonymous
    November 02, 2011
    Thanks, was very helpful.

  • Anonymous
    December 19, 2011
    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?

  • Anonymous
    December 19, 2011
    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

  • Anonymous
    April 10, 2013
    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