Get-DbaAgentJob,Get-dbaAgentJobStep,dbatools.
This command I have written to get only "@Directory" value alone and want to update it with a new value. However updating it with a new value not handled yet on this. As the command is straight forward with which we can update(Set-DbaAgentJobStep) .The challenge here how to extract "@Directory" value alone and how to change it.
The query below will update "@Directory" value in all the requird jobs at a time.
The query will modified teh @directory value in jobs in more than two servers.
$sever = "MAPS\PROD" $newpath = 'D:\BACKUPS' $job = Get-DbaAgentJob ` -SqlInstance $sever ` -Verbose |Where-Object {$_.Name -like '*DatabaseBackup - USER_DATABASES - FULL*'}|Select-Object Name $directory = Get-DbaAgentJobStep ` -SqlInstance $sever ` -Job $job.Name ` -Verbose|Select-Object -ExpandProperty Command $reqstring = ($directory | Select-String -Pattern "@Directory\s*=\s*'([^']*)" -AllMatches).Matches.Groups[1].value $directory.Replace($reqstring,$newpath)
$server = "MAPS\PROD" $newpath = "X:\OLAHALEN\BACKUPS" $jobs = Get-dbaAgentJob ` -SqlInstance $server ` -Verbose|Where-Object {((($_.Name -like '*USER_DATABASES*')-OR($_.Name -like '*SYSTEM_DATABASE*'))-AND($_.Name -notlike '*Index*'))-and ($_.Name -notlike '*integrity*')} Select-Object Name,StartStepId,SQLInstance $jobs $jobs|ForEach-Object{ $indjob = $_ $jobstep = Get-DbaAgentJobStep ` -SqlInstance $server ` -Job $indjob.Name ` -Verbose|Select-Object -ExpandProperty Command $replacevalue = ($jobstep|Select-String -Pattern "@Directory\s*=\s*'([^']*)" -AllMatches).Matches.Groups[1].Value $replacevalue $newvalue = ($jobstep.Replace($replacevalue,$newpath)) Set-DbaAgentJobStep ` -SqlInstance $server ` -Job $indjob.Name ` -StepName $indjob.Name ` -Command $newvalue ` -Verbose}
$servers = @("MAPS\PROD","MAPS\TEST") $servers|ForEach-Object{ $indServer = $_ $newpath = "T:\OLAHALEN\BACKUPS" $jobs = Get-dbaAgentJob ` -SqlInstance $indServer ` -Verbose|Where-Object {((($_.Name -like '*USER_DATABASES*')-OR($_.Name -like '*SYSTEM_DATABASE*'))-AND($_.Name -notlike '*Index*'))-and ($_.Name -notlike '*integrity*')} Select-Object Name,StartStepId,SQLInstance $jobs $jobs|ForEach-Object{ $indjob = $_ $jobstep = Get-DbaAgentJobStep ` -SqlInstance $indServer ` -Job $indjob.Name ` -Verbose|Select-Object -ExpandProperty Command $replacevalue = ($jobstep|Select-String -Pattern "@Directory\s*=\s*'([^']*)" -AllMatches).Matches.Groups[1].Value $replacevalue $newvalue = ($jobstep.Replace($replacevalue,$newpath)) Set-DbaAgentJobStep ` -SqlInstance $indServer ` -Job $indjob.Name ` -StepName $indjob.Name ` -Command $newvalue ` -Verbose}}
Comments