help needed in power shell script to script out sql server jobs

• mhafiez

  Old Hand

  Points: 369

  More actions

  September 23, 2013 at 8:46 am

  #278224

  Hello,

  I created this script to script out SQL server jobs, the script creates a file matching the job name and script it out as a sql script.

  the script works most of the time, the only time it generates exception, when I have non conventional job names like "load/MTS Error Check" for example where I have "/", so I tried to add some logic to replace those wearied characters without lock, I want to change the file names not the job name on the server.

  SQL server replication throws the same exception

  "Out-File : The given path's format is not supported."

  what I'm doing wrong?.

  Import-Module SQLPS -DisableNameChecking

  #Script out all job steps and created a file per job

  #created by Mulham Hafiez on 9/18/2013

  #map a drive to a list of servers

  New-PSDrive -Name UNCPath -PSProvider FileSystem -Root \\Share1\jobs

  foreach ($instancename in get-content -path Microsoft.PowerShell.Core\FileSystem::\\Share1\Servers\Servers.txt)

  {

  $Date =Get-date -Format "yyyy-M-d_hmmtt"

  $folder= "UNCPath:\servers\jobs"

  $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $instanceName

  #extract each job the outer foreach loop and script it

  foreach ($job in $server.JobServer.Jobs)

  {

  #IF the output folder does not exist then create it

  $OutputFolder = "$folder\$instancename\"

  $DoesFolderExist = Test-Path $OutputFolder

  $null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

  # $job | foreach {$_.Script()} | out-file $fullpath

  $JobName =$job.Name

  # remove non conventional charachters

  $JobName |ForEach-object {

  $_ -replace "\\",'_' `

  -replace "//" ,'_'`

  }

  $jobNameFile = $jobName+"_"+$date+".sql"

  $fullpath =Join-Path $OutputFolder $jobNameFile

  $job | foreach {$_.Script()} | Out-File $fullpath

  }

  }