Thursday, March 8, 2012

[Powershell] Script out each job to a separate file

Code blatantly stolen from both John Sansom and Jan Goyvaerts (mostly John; read the credits), but I modified it a bit.
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
}
}