SQL Server Management Studio can script jobs. Recently a colleague asked if we could script all the jobs at once. Actually, using SSMS, we can script all the jobs or even selected jobs.
You may have noticed that SSMS does not allow selecting multiple jobs within the expanded jobs section of Object Explorer. You may select jobs, however, within the Object Explorer Details view. Select Object Explorer Details. When the Object Explorer Details window opens. Select the desired jobs. You can then right-click and chose to script the selected jobs to a new Query Editor Window, a file or the clipboard.
Once you have a file with the combined jobs in it, you can split the job scripts into separate files via a PowerShell script by keying on the comment “/***** Object: Job”. The file names can even be picked up from the comments.
PowerShell Script to Break Up the Files
# Split file into multiple files when you see the comment “/****** Object: Job” followed by the job name in square brackets
$filename=”C:\Users\andrea\Documents\JobScripts\All-Jobs-Dev.sql”
$OutputDirectory= “C:\Users\andrea\Documents\JobScripts\” # Notice the trailing slash
# Create the path if necessary. You can force (-force) the creation if you want to create additional directory layers. But -force may recreate existing directories.
if (! (Test-Path $OutputDirectory)) {
New-Item -Path $OutputDirectory -ItemType Directory
}
# Our job scripts have embedded server names. Sometimes more than one version we might need to change.
$existingServer=@(‘dev-dbsrvr.mydomain.edu’,’dev-host.cri.mydomain.edu’)
$newServer=’qa-dbsrvr.mydomain.edu’
# And SSIS package environment references change between environments.
$existingenvref=@(‘ENVREFERENCE 16′,’ENVREFERENCE 15’)
$newenvref=@(‘ENVREFERENCE 1’, ‘ENVREFERENCE 2′)
# Read in the file with all the jobs.
$filecontent= get-content $filename
# The first file will just contain USE MSDB from the top of the file.
$newfile=$outputDirectory+’UseMSDB.sql’
# Loop over all the lines in the file replacing server names and environment references as needed.
ForEach ($line in $filecontent) {
for ($i=0; $i -le 1; $i++) {
$line=$line.replace($($existingServer[$i]),$newServer)
}
for ($i=0; $i -le 1; $i++) {
$line=$line.replace(“$($existingenvref[$i])”,”$($newenvref[$i])”)
}
# When you see “***** Object: Job” a new job is starting.
If ($line -match “^/\*\*\*\*\*\* Object: Job “) {
$JobName = [regex]::match($line,’\[([^\)]+)\]’).Groups[1].Value
# Create a new file named CreateJob_ Followed by the job name and ending in .sql
$newfile = $outputDirectory+”CreateJob_”+$JobName+”.sql”
Write-Host Creating file $newfile
‘USE MSDB’ | Out-File -Append $newfile
‘GO’| Out-File -Append $newfile
$line | Out-File -Append $newfile
}
Else {
$line | Out-File -Append $newfile
}
}