PowerShell code to script out all SQL Server Agent jobs into a single file

I was trying to script out all the SQL Server Agent jobs for category ‘Data Warehouse’ into a single file

I was able to do it using PowerShell, where every single job creates a single file.

But I need one file for all the SQL Server Agent jobs under category ID = 100 (or Category : = ‘Data Warehouse’)

Code I’m currently using:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$serverInstance = "APAAUHC7DB01VD"

$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance

$jobs = $server.JobServer.Jobs 
#$jobs = $server.JobServer.Jobs | where-object {$_.category -eq "100"}

if ($jobs -ne $null)
{

$serverInstance = $serverInstance.Replace("", "-")

ForEach ( $job in $jobs )
{
$FileName = "C:SQLBackupSQLJobs" + $serverInstance + "_" + $job.Name + ".sql"
$job.Script() | Out-File -filepath $FileName
}
}

Answer

Give $FileName a single file name for the whole set. Then you can leave out the whole foreach block:

$FileName = "C:SQLBackupSQLJobswhatever.sql"

$jobs | %{ $_.Script() } | Out-File -filepath $FileName

Advertisement

Answer