Scripting Out Jobs

Question

Post reply

Scripting Out Jobs

Colin Betteley

Hall of Fame

Points: 3095
More actions
December 16, 2011 at 7:28 am

#252163

I have created an osql task that runs SP_Help_Revlogin to write Login details to a file for DR purposes. This runs as a scheduled job to ensure I always have current data
I would like to do something similar for Scheduled Jobs and want to automate the process. I know that I can do this by right clicking the Jobs and selecting script option- but this is a manual process.
Try as I might I cannot find any code to do this. Which is strange for such an important task. My only option at the moment seems to be to learn Powershell. Something which is good in itself, but seems a little harsh to resolve such a "simple" issue.
Any hints on how I can automate the scripting out of Jobs whilst I learn Powershell over Christmas?
Thanks
Colin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

I use this.

<#

.SYNOPSIS

Function to script all SQL jobs

.DESCRIPTION

Writes all SQL Jobs out to a .sql file in the specified folder

.EXAMPLE

Script-AllSQLJobs -Instance "Server\Instance -Folder "C:\JobScripts\"

.NOTES

Requires SQL SMO to be installed

.LINK

#>

function Script-AllSQLJobs ([string]$Instance, [string]$Folder)

{

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Instance

$srv.JobServer.Jobs | foreach {

$filename = $Folder + $_.ToString().replace(":","").replace("/","").replace("""","").replace("\","").replace("*","").replace("?","").replace("<","").replace(">","").replace("|","") + ".sql";

$_.Script() | out-file -FilePath $filename -force

}

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 2

Thanks,

Could you give a bit more information on how you store/run the script?

Regards

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 3

I've added a few extra lines.

<#

.SYNOPSIS

Function to script all SQL jobs

.DESCRIPTION

Writes all SQL Jobs out to a .sql file in the specified folder

.EXAMPLE

Script-AllSQLJobs -Instance "Server\Instance -Folder "C:\JobScripts"

.NOTES

Requires SQL SMO to be installed

.LINK

#>

function Script-AllSQLJobs ([string]$Instance, [string]$Folder)

{

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Instance

$srv.JobServer.Jobs | foreach {

$filename = $Folder + $_.ToString().replace(":","").replace("/","").replace("""","").replace("\","").replace("*","").replace("?","").replace("<","").replace(">","").replace("|","") + ".sql";

$_.Script() | out-file -FilePath $filename -force

}

Script-AllSQLJobs -Instance "Server1" -folder "C:\SQLJobs\Server1\"

Script-AllSQLJobs -Instance "Server2" -folder "C:\SQLJobs\Server2\"

Script-AllSQLJobs -Instance "Server3" -folder "C:\SQLJobs\Server3\"

You can edit the function call lines as you see fit. Save the code as .ps1 file. This can then be called from any SQL Server agent powershell job step or using a cmdshell jobstep simply by calling it using a fully qualified path much like a batch file.

cmdshell will require prefixing with the powershell keyword for it to work.

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 4

Thank you!

The mist lifts and the view is great.

Enjoy the holiday season.

Colin

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 5

After much trial and error I have created a SQL Job which runs the following command. The Job states that it runs successfully, but no file is generated in the named Directory. Whatam I doing wrong? Thanks.

PS "E:\Microsoft SQL Server\CORPDB_2008_Back_Ups\Back_Up_Jobs.ps1"

"-argument1 ServerName\InstanceName"

"-argument2 E:\Microsoft SQL Server\CORPDB_2008_Back_Ups"

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

Try taking out the quotes.

Or read this and search for ampersand. http://technet.microsoft.com/en-us/library/ee176949.aspx

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 7

It is interesting to note that when creating the SQL Job that you do NOT use the Powershell option but the Operating System(CmdExec) option and precede the command with Powershell.exe rather than PS.

As ever, Microsoft like to make things intuitive! 🙂

Posted to help anybody else who comes across this thread

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 8

Colin Betteley (12/21/2011)
It is interesting to note that when creating the SQL Job that you do NOT use the Powershell option but the Operating System(CmdExec) option and precede the command with Powershell.exe rather than PS.
As ever, Microsoft like to make things intuitive! 🙂
Posted to help anybody else who comes across this thread

Thats a feature of command shell. You need to start the powershell app to run the script. If you create a SQL Job with a step type of Powershell (SQL 2008+) you dont need to preceede the script with powershell.exe as the job step starts the mini shell itself.