I needed to be able to script out each of my jobs to separate files. And I need to trap for characters that can't be used in a file name (why, oh why, did I ever put Colons in my job names?)
(updated on Pi Day - forgot numbers, which caused some jobs to not script out)
# Date: 23/02/12 updated 2012/03/14
# Author: John Sansom
# Description: PS script to generate all SQL Server Agent jobs on the given instance.
# The script accepts an input file of server names.
# Version: 1.1 mbourgon modified to save each job to a separate file; excludes non-filename characters
# 1.11 added numbers.
# Example Execution: .\Create_SQLAentJobSripts.ps1 .\ServerNameList.txt
param([String]$ServerListPath)
#Load the input file into an Object array
$ServerNameList = get-content -path $ServerListPath
#Load the SQL Server SMO Assemly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
#For each server in the array do the following..
foreach($ServerName in $ServerNameList)
{
Try
{
$objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
$objSQLConnection.Open() | Out-Null
Write-Host "Success."
$objSQLConnection.Close()
}
Catch
{
Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
$errText = $Error[0].ToString()
if ($errText.Contains("network-related"))
{Write-Host "Connection Error. Check server name, port, firewall."}
Write-Host $errText
continue
}
#IF the output folder does not exist then create it
$OutputFolder = ".\$ServerName"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}
#Create a new SMO instance for this $ServerName
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
#Script out each SQL Server Agent Job for the server
$jobs = $srv.JobServer.Jobs
#Using regex invocation courtesy of Jan Goyvaerts at regular-expressions.info
$regex = [regex] '[^a-zA-Z0-9-\ ]'
foreach ($job in $jobs)
{
$jobname = ".\$OutputFolder\" + $regex.Replace($Job.Name,'_') + ".job.sql"
$job.Script() | Out-File $jobname
}
}