Copy-dbaAgentJob incorrectly escapes @server varianble on jobsteps

[bzuidgeestITW]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

The specified '@srv' is invalid (valid values are returned by sp_helpserver).

Steps to Reproduce

Copy-DbaAgentJob -Source $sourceServer -Destination $destinationServer -Job $jobname -DisableOnDestination

Please confirm that you are running the most recent version of dbatools

2.0.4

Other details or mentions

I believe line 260 is responsible for the problem. The value for @server is created as s0560\POWERBI and then escaped to s0560\powerbi

sp_helpserver does not supply that as a valid value.

line 260 in copy-dbaAgentJob

 $sql = $sql -replace [Regex]::Escape("@server=N'$($sourceserver.DomainInstanceName)'"), [Regex]::Escape("@server=N'$($destServer.DomainInstanceName)'")

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.14393.5582
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.5582
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

SQL 2022

.NET Framework Version

[aRickInTheMud]

I'm not sure if github maybe changed the formatting on your backslashes. Did you mean s0560\POWERBI was getting changed to s0560\\POWERBI? Because that would make sense and does seem like a bug. I don't normally see regex escapes on the replacement side.

Looks like this was fixed once before in 2017: https://github.com/dataplat/dbatools/pull/1322 Specific commit here: https://github.com/dataplat/dbatools/pull/1322/commits/1775822742657e2deffbd5bd3e7d2b95d23f2f50

It was changed again in 2019 and that introduced the escapes on the destination side: https://github.com/dataplat/dbatools/pull/5979

Do you have a remote job server setup? When I script out a job I see something like msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)', but the regex on line 260 is looking for @server=N'...', which is a different name and would not match the spaces anyway. Did your error mention @server or @srv as invalid? I'm getting confused on the names.

The only command I can find that uses @server is sp_add_jobstep. But the documentation says it isn't used yet. What kind of steps were in the job you were trying to copy?

[bzuidgeest]

I put this down almost a year ago... I do recognize that most of you might be building this as volunteers, but my memory barely goes back that far :). I'm mostly a C# programmer and rarely have need for dba-tools scripts (but they are handy when called for)

For what I remember (looks like github markdown messed with slashes to). I was copying jobs between sql instances on a cluster. Hence servername slash instancename. but the regex in your script takes the slash and escapes it. causing the script to fail.

That is about as much as I remember. I do appreciate people putting tools like dba-tools on the internet. But I moved on to other tasks since then. Maybe you can still look into this for the next guy or just close it and prioritize something someone is still actively waiting on. Either way thank you for responding.

[potatoqualitee]

thanks for the feedback, all. @claude pls review and propose a solution

[claude[bot]]

Claude finished @potatoqualitee's task —— View job

---

Analysis and Proposed Solution

• [x] Read repository guidelines (CLAUDE.md)
• [x] Locate and read Copy-DbaAgentJob.ps1
• [x] Analyze the problematic lines 366 and 369
• [x] Research the correct usage of Regex.Escape
• [x] Propose a solution

---

Problem Summary

The issue is in Copy-DbaAgentJob.ps1 at lines 366 and 369. The code incorrectly uses [Regex]::Escape() on the replacement side of a -replace operation:

# Line 366 - INCORRECT
$sql = $sql -replace [Regex]::Escape("@owner_login_name=N'$missingLogin'"), [Regex]::Escape("@owner_login_name=N'$saLogin'")

# Line 369 - INCORRECT (reported in issue)
$sql = $sql -replace [Regex]::Escape("@server=N'$($sourceserver.DomainInstanceName)'"), [Regex]::Escape("@server=N'$($destServer.DomainInstanceName)'")

Root Cause

When DomainInstanceName contains a backslash (e.g., s0560\POWERBI), the [Regex]::Escape() on the replacement value double-escapes the backslash:

• Input: s0560\POWERBI
• After Regex.Escape: s0560\\POWERBI
• Result in SQL: @server=N's0560\\POWERBI'

This causes SQL Server to reject it because sp_helpserver doesn't recognize the double-backslash format.

Why This Is Wrong

In PowerShell's -replace operator:

• Left side (pattern): Should use [Regex]::Escape() to treat special regex characters literally
• Right side (replacement): Should NOT use [Regex]::Escape() - the replacement string is already treated as literal (except for $ backreferences)

Using [Regex]::Escape() on the replacement side causes PowerShell to insert the escaped characters literally into the result, which is not what we want.

Historical Context

As mentioned by @aRickInTheMud:

• Fixed in 2017: PR #1322 removed the escape from the replacement side
• Re-introduced in 2019: PR #5979 incorrectly added it back

Proposed Solution

Remove [Regex]::Escape() from the replacement side while keeping it on the pattern side:

# Line 366 - CORRECTED
$sql = $sql -replace [Regex]::Escape("@owner_login_name=N'$missingLogin'"), "@owner_login_name=N'$saLogin'"

# Line 369 - CORRECTED
$sql = $sql -replace [Regex]::Escape("@server=N'$($sourceserver.DomainInstanceName)'"), "@server=N'$($destServer.DomainInstanceName)'"

This will:

1. ✅ Correctly match the pattern in the SQL script (left side escaped)
2. ✅ Replace with the unescaped destination value (right side NOT escaped)
3. ✅ Handle instance names with backslashes properly (e.g., s0560\POWERBI)
4. ✅ Produce valid SQL that sp_helpserver recognizes

Files to Change

/home/runner/work/dbatools/dbatools/public/Copy-DbaAgentJob.ps1

• Line 366: Remove [Regex]::Escape() from second argument
• Line 369: Remove [Regex]::Escape() from second argument

---

Ready to implement - Let me know if you'd like me to make these changes and create a PR.

---

[potatoqualitee]

@claude pls do

[claude[bot]]

Claude finished @potatoqualitee's task —— View job • claude/issue-9086-20251128-1752 • Create PR ➔

---

Fix Implemented Successfully

• [x] Read repository guidelines (CLAUDE.md)
• [x] Read Copy-DbaAgentJob.ps1 to verify current state
• [x] Fix line 366: Remove [Regex]::Escape() from replacement side
• [x] Fix line 369: Remove [Regex]::Escape() from replacement side
• [x] Check for related tests
• [x] Commit and push changes
• [x] Provide PR creation link

---

Changes Made

Copy-DbaAgentJob.ps1 (lines 366, 369):

Removed [Regex]::Escape() from the replacement side of -replace operations. This fixes the double-escaping issue where instance names with backslashes (e.g., s0560\POWERBI) were incorrectly escaped to s0560\\POWERBI, causing SQL Server to reject them with the error: "The specified '@srv' is invalid (valid values are returned by sp_helpserver)."

Root Cause:

In PowerShell's -replace operator:

• Left side (pattern): Should use [Regex]::Escape() to treat special regex characters as literals
• Right side (replacement): Should NOT use [Regex]::Escape() - it's already treated as literal

Using [Regex]::Escape() on the replacement side causes the backslash to be double-escaped in the output SQL script.

Historical Context:

This issue was previously fixed in PR #1322 (2017) but was re-introduced in PR #5979 (2019).

---

| Branch