Enable or Disable BizTalk SQL Jobs using Powershel Script

Welcome back to another PowerShell script nice article!!

This script would be helpful to enable or disable BizTalk SQL jobs in a click only, easy to run either lower or production environment. At the bottom of the post, you can see all the file name.

Considering Points: 

> Login user should be part for BizTalk Administrator Group

> $instancename - Use your environment instance name

> $sqljoblist - Update path as per BTSqlJobs.txt file

> $logpath - Provide path directory

> In case of more than one BizTalk SQL instances, copy scripts to other instance and update BT Sql job(s) in 'BTSQLJobs' file

Enable BizTalk SQL Jobs: (File- psEnableBTSQLJobs.ps1)

param(
[string] $instancename='LAPTOP-CF8KHTV1',
[string] $sqljoblist = 'D:\Dell\Biztalk\BizTalkLive\BizTalkPowerShellService\StarStopBTSQLJobs\BTSqlJobs.txt',
[string] $logpath = 'D:\Dell\Biztalk\BizTalkLive\BizTalkPowerShellService\StarStopBTSQLJobs\BTSQLJobsActivityLogs.txt',
[string] $successjoblist = "",
[string] $failedjoblist = "",
[string] $writelog = ""
)
 
Start-Transcript -path $logpath -Append
 
[System.Reflection.Assembly]::LoadwithPartialName('Microsoft.SqlServer.SMO') | out-null
$serverinstance = New-Object Microsoft.SqlServer.Management.SMO.Server("$instancename")
 
$sqljobs = New-Object System.IO.StreamReader($sqljoblist)
while (($sqljob = $sqljobs.ReadLine()) -ne $null)
{
try{
#To enable BizTalk SQL Job
$job = $serverinstance.jobserver.jobs["$sqljob"]
    if($job.IsEnabled -ne "TRUE"){
     $job.IsEnabled = $true
     $job.Alter()
     $successjoblist = $successjoblist+$sqljob+"`r`n"
     }
}
catch{
    $failedjoblist = $failedjoblist + 'SQL Job- '+$sqljob+ ' caught exception:'+ $_ + ' at datetime: '+(Get-Date).ToString()+"`r`n"
}
}#End while
 
if(($sqljobs.EndOfStream) -eq $true){
    $sqljobs.Dispose()
}
 
if($successjoblist.Length -gt 0){
    $writelog='---------------------Success---------------------'+"`r`n"+$successjoblist+'---------------------Success---------------------'
    Write-Host $writelog
    $writelog =""
}
 
if($failedjoblist.Length -gt 0){
    $writelog='---------------------Error---------------------'+"`r`n"+$failedjoblist+'---------------------Error---------------------'
    Write-Host $writelog
    $writelog =""
}

if(($failedjoblist.Length -lt 1) -AND ($successjoblist.Length -lt 1)){
    $writelog='---------------------No-Run---------------------'+"`r`n"+'SQL Job(s) are already enabled !'+"`r`n"+'---------------------No-Run---------------------'
    Write-Host $writelog
    $writelog =""
}
 
Stop-Transcript 

Disable BizTalk SQL Jobs: (File- psDisableBTSQLJobs.ps1)

param(
[string] $instancename='LAPTOP-CF8KHTV1',
[string] $sqljoblist = 'D:\Dell\Biztalk\BizTalkLive\BizTalkPowerShellService\StarStopBTSQLJobs\BTSqlJobs.txt',
[string] $logpath = 'D:\Dell\Biztalk\BizTalkLive\BizTalkPowerShellService\StarStopBTSQLJobs\BTSQLJobsActivityLogs.txt',
[string] $successjoblist = "",
[string] $failedjoblist = "",
[string] $writelog = ""
)
 
Start-Transcript -path $logpath -Append
 
[System.Reflection.Assembly]::LoadwithPartialName('Microsoft.SqlServer.SMO') | out-null
$serverinstance = New-Object Microsoft.SqlServer.Management.SMO.Server("$instancename")
 
$sqljobs = New-Object System.IO.StreamReader($sqljoblist)
while (($sqljob = $sqljobs.ReadLine()) -ne $null)
{
try{
#To enable BizTalk SQL Job
$job = $serverinstance.jobserver.jobs["$sqljob"]
    if($job.IsEnabled -eq "TRUE"){
     $job.IsEnabled = $false
     $job.Alter()
     $successjoblist = $successjoblist+$sqljob+"`r`n"
     }
}
catch{
    $failedjoblist = $failedjoblist + 'SQL Job- '+$sqljob+ ' caught exception:'+ $_ + ' at datetime: '+(Get-Date).ToString()+"`r`n"
}
}#End while
 
if(($sqljobs.EndOfStream) -eq $true){
    $sqljobs.Dispose()
}
 
if($successjoblist.Length -gt 0){
    $writelog='---------------------Success---------------------'+"`r`n"+$successjoblist+'---------------------Success---------------------'
    Write-Host $writelog
    $writelog =""
}
 
if($failedjoblist.Length -gt 0){
    $writelog='---------------------Error---------------------'+"`r`n"+$failedjoblist+'---------------------Error---------------------'
    Write-Host $writelog
    $writelog =""
}
 
if(($failedjoblist.Length -lt 1) -AND ($successjoblist.Length -lt 1)){
    $writelog='---------------------No-Run---------------------'+"`r`n"+'SQL Job(s) are already disab !'+"`r`n"+'---------------------No-Run---------------------'
    Write-Host $writelog
    $writelog =""
}
 
Stop-Transcript

BizTalk Jobs List: (File- BTSqlJobs.txt)

Backup BizTalk Server (BizTalkMgmtDb)
CleanupBTFExpiredEntriesJob_BizTalkMgmtDb
MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
MessageBox_Message_ManageRefcountLog_BizTalkMsgBoxDb
MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
MessageBox_Updatestats_BizTalkMsgBoxDb
Monitor BizTalk Server (BizTalkMgmtDb)
Operations_OperateonInstances_OnMaster_BizTalkMsgBoxDb
PurgeSubscriptionsJob_BizTalkMsgBoxDb
Rules_Database_Cleanup_BizTalkRuleEngineDb
TrackedMessages_Copy_BizTalkMsgBoxDb
DTA Purge and Archive (BizTalkDTADb)

Batch Script to Run Powershell Script- Enable Jobs: (File- psEnableBTSQLJobs.ps1)

@ECHO OFF
PowerShell.exe -ExecutionPolicy Bypass D:\Dell\Biztalk\BizTalkLive\BizTalkPowerShellService\StarStopBTSQLJobs\psEnableBTSQLJobs.ps1
ECHO Press any key to exit..
PAUSE >NUL
CLS
EXIT /B

Batch Script to Run Powershell Script- Disable Jobs: (File- psDisableBTSQLJobs.ps1)

@ECHO OFF
PowerShell.exe -ExecutionPolicy Bypass D:\Dell\Biztalk\BizTalkLive\BizTalkPowerShellService\StarStopBTSQLJobs\psDisableBTSQLJobs.ps1
ECHO Press any key to exit..
PAUSE >NUL
CLS
EXIT /B

!! Keep visiting BizTalkLive !!