December 16, 2011 at 7:28 am
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
December 16, 2011 at 8:30 am
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
}
}
December 16, 2011 at 9:01 am
Thanks,
Could you give a bit more information on how you store/run the script?
Regards
December 16, 2011 at 9:13 am
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.
December 16, 2011 at 9:28 am
Thank you!
The mist lifts and the view is great.
Enjoy the holiday season.
Colin
December 20, 2011 at 3:40 am
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"
December 20, 2011 at 3:47 am
Try taking out the quotes.
Or read this and search for ampersand. http://technet.microsoft.com/en-us/library/ee176949.aspx
December 21, 2011 at 3:39 am
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
December 21, 2011 at 4:11 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply