Script SQL Server Agent Jobs Using PowerShell

It’s that time of the month again, no not that one, it’s T-SQL Tuesday time and this weeks topic is hosted by Wayne Sheffield (blog|twitter).

If you don’t know what T-SQL Tuesday is, you’re either new to the SQL community or have had your head buried in the sand. I’m not going to repeat what others have said a thousand times over, as much as I do love the sound of my own voice, but essentially it’s a cracking excuse for SQL folk to Blog and discuss a common topic. If you’re a member of the Twitterverse you can follow the discussion online using the #TSQL2sDay hash tag.

This months theme is “blog about using PowerShell for doing, well, anything as long as it is related to SQL Server or the computer that SQL Server runs on”.

I’m currently busy breaking our Production environment and so rather than miss this months theme I’m re-sharing a previous post (in case you’re wondering about comment dates etc.) .

Script SQL Server Agent Jobs Using PowerShell

As many of you already know, I’m a big fan of Automating Everything and so I’ve been learning to use PowerShell with SQL Server.

Interested to know what other DBAs are using PowerShell for, I reached out to the Twitterverse for insight.

@JohnSansom I use #powershell for #configuration purposes and quick #inventory of sqlserver stuff (settings, statistics, DMV queries, …)

— Johan Bijnens (@alzdba) February 27, 2012

@JohnSansom all sorts :) monitoring, data collection, general windows admin (AD stuff adding users computers etc), installs, etc…

— James Boother (@JimmyBoo) February 27, 2012

Clearly there are a lot of clever folks out there already taking advantage of PowerShell to administer SQL Server.

I think that I have now got to a point in my own learning where I’m dangerous enough with PowerShell for it to be worth sharing some of this knowledge with you. I recently had a need to script out all SQL Server Agent Jobs for a large number of instances. Deflated by the thought of having to do so manually, I instead chose to turn to some PowerShell magic in order to accomplish the task. Below you will find the PowerShell script to achieve this.

PowerShell Code to Script Out All SQL Server Agent Jobs

# Date:		16/02/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
#
# Example Execution: .\Create_SQLAgentJobSripts.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
	$srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file ".\$OutputFolder\jobs.sql"

	#Use the command below to output each SQL Agent Job to a separate file. Remember to comment out the line above.
        #Removed backslash character, typically seen in Replication Agent jobs, to avoid invalid filepath issue
	#$srv.JobServer.Jobs | foreach-object -process {out-file -filepath $(".\$OutputFolder\" + $($_.Name -replace '\\', '') + ".sql") -inputobject $_.Script() }
}

If you would you like to read more posts on PowerShell let me know.