Script Drops on all SQL Agent Jobs

I am new to powershell and I am trying to script out all S开发者_如何学JAVAQL agent jobs. I have found a piece of code that does that thanks to ENRIQUE at SOLID QUALITY MENTORS.

My question is, how do I script a if exists, drop for every job? Options.ScriptJobs does not seem to do what I think it should do?

param([string]$serverName,[string]$jobNameFile)

function script-SQLJobs([string]$server,[string]$jobNameFile) { [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("$server") 
$db  = New-Object Microsoft.SqlServer.Management.Smo.Database 
$scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv) 
$scrp.Options.ScriptDrops = $TRUE 
$scrp.Options.WithDependencies = $TRUE 


$jobNameFile = "C:\SQLJOBS\Jobs.sql"
remove-item $jobNameFile

$jobs = $srv.JobServer.get_Jobs() 
$jobs=$jobs | Where-Object {$_.Name -notlike "sys*"}     

foreach($job in $jobs) 
{   
    $script=$job.Script() 
    $script >> $jobNameFile
    "GO" >> $jobNameFile 

} 

}

script-SQLJobs $serverName $jobNameFile

Many Thanks.

You could exclude scripts that do not match the words 'drop table'. For example:

$srv.JobServer.Jobs | Where-Object {$_.Name -notlike "sys*"} | Foreach-Object{
        $script = $_.Script()

        if($script -notmatch 'DROP TABLE')
        {
            $script+ "`nGO`n"
        }

} | Out-File $jobNameFile

Another (cosmetic) option would be to check all job steps command:

$srv.JobServer.Jobs | Where-Object {$_.Name -notlike "sys*"} | Foreach-Object{

    $cmd = $_.JobSteps | select -expand Command 

        if($cmd -match 'DROP TABLE')
        {
            $_.script()+ "`nGO`n"
        }       
} | Out-File $jobNameFile

You need to provide your script options object to the script method:

$script=$job.Script($scrp)

Here is a Powershell script copied from http://www.johnsansom.com/script-sql-server-agent-jobs-using-powershell/ which has been extended to do what you want.

# 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 "Servers.txt"
#$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
    foreach($job in $srv.JobServer.Jobs)
    {
    Write-Host $job.Name

    $script = ""
    $script = $script + "-- Uninstall the job" + "`r`n"
    $script = $script + "DECLARE @jobId binary(16)" + "`r`n"
    $script = $script + "SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'$job')" + "`r`n"
    $script = $script + "IF (@jobId IS NOT NULL)" + "`r`n"
    $script = $script + "BEGIN" + "`r`n"
    $script = $script + "    EXEC msdb.dbo.sp_delete_job @job_id=@jobId, @delete_unused_schedule=1" + "`r`n"
    $script = $script + "END" + "`r`n"
    $script = $script + "GO`r`n"
    $script = $script + "`r`n"
    $script = $script + "-- Install the job" + "`r`n"
    $script = $script + $job.Script()
    $script = $script + "GO`r`n"

    $fileName = $job.Name -replace '\\', ''

    $script | out-file ".\$OutputFolder\$fileName.sql"
    }
}

NOTE: You don't actually want to use the SMO created DROP command because it is dependent on the job id, which makes the resulting script non-reusable.