Enable or Disable BizTalk SQL Jobs

BizTalk Enable or Disable BizTalk SQL Jobs

Welcome back to another article on PowerShell scripting! This script will help you easily enable or disable BizTalk SQL jobs in both lower and production environments. You can find the file names at the bottom of this post.

Considering Points:

> Login user should be part of 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 instance, copy scripts to other instances and update BT SQL job(s) in the ‘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

Previous Lesson

Back to Course

Next Lesson

BizTalk