• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

Script SQL Server Agent Jobs Using PowerShell

February 12, 2013 by John Sansom 46 Comments

TSQL Tuesday LogoIt’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.

Filed Under: Administration, SQLServerCentral Syndication, SQLServerPedia Syndication Tagged With: Automation, Powershell, SQL Server Agent

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy