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.
$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)
The query below will update "@Directory" value in all the requird jobs at a time.
$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}
The query will modified teh @directory value in jobs in more than two servers.
$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

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server