Backup-DbaDbMasterKey tries to create a backup file with invalid characters

[rgl]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

I received no errors, which is completely unexpected.

Instead, it tried to create the backup using a file named NP:.$SQLSERVER-master-masterkey which has an invalid character (colon).

Steps to Reproduce

$sqlInstance = Connect-DbaInstance `
    -SqlInstance .\SQLSERVER `
    -AppendConnectionString "HostNameInCertificate=db.test"
Backup-DbaDbMasterKey `
    -SqlInstance $sqlInstance `
    -SecurePassword $securePassword

Which outputted:

ComputerName : test
InstanceName : SQLSERVER
SqlInstance  : test\SQLSERVER
Database     : master
Path         : D:\SQLServer\Data\MSSQL16.SQLSERVER\MSSQL\Backup\NP:.$SQLSERVER-master-masterkey
Status       : Success

But listing the resulting file, we can see a zero byte length file, with a partial name NP (instead of the invalid filename NP:.$SQLSERVER-master-masterkey):

dir D:\SQLServer\Data\MSSQL16.SQLSERVER\MSSQL\Backup

    Directory: D:\SQLServer\Data\MSSQL16.SQLSERVER\MSSQL\Backup


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       19/07/2023     12:34              0 NP

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

2.0.4

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name                           Value
----                           -----
PSVersion                      5.1.17763.3770
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.3770
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

PSChildName Version
----------- -------
Client      4.8.03761
Full        4.8.03761
Client      4.0.0.0

[andreasjordan]

Can you try to not use named pipes but TCP/IP? Using -SqlInstance $env:COMPUTERNAME\SQLSERVER should help.

[rgl]

@andreasjordan, maybe I'm missing something, but what is the difference between .\SQLSERVER and $env:COMPUTERNAME\SQLSERVER when running in the same machine? Using that syntax, how do you deduce the used protocol?

[andreasjordan]

That is part of the used datatype for that parameter. It is a custom type with a lot of functionallity.
Source code: https://github.com/dataplat/dbatools.library/blob/main/project/dbatools/Parameter/DbaInstanceParameter.cs

[rgl]

Thanks for pointing that C# class, it cleared things up!

And Indeed, using $env:COMPUTERNAME\SQLSERVER worked around the problem. It now correctly creates the backup files. Thank You!

Despite the workaround, I think this is a bug, and it should have saved the files or raised an exception.

FWIW, I wasn't expecting a difference between .\SQLSERVER and $env:COMPUTERNAME\SQLSERVER, as they should be the same when we connect from the same machine where SQL Server is installed on.

[andreasjordan]

I agree that .\SQLSERVER would be a nice shortcut. Maybe @FriedrichWeinmann can give more background information.

The "impossible" filename when using named pipes is indeed a bug so I will let this issue open and try to find some time to implement a fix for that.

[andreasjordan]

@FriedrichWeinmann - as you are currently working on the library: Can you have alook at this issue?

[andreasjordan]

Let me ping you again on this one, @FriedrichWeinmann ...

[andreasjordan]

Let's do one more ping... @FriedrichWeinmann

[potatoqualitee]

this may have been fixed recently, moving to the dbatools.library and will put on todo for claude.

[potatoqualitee]

@claude please investigate and propose a PR

[claude]

Claude encountered an error —— View job

---

I'll analyze this and get back to you.

[claude]

Claude finished @potatoqualitee's task —— View job • claude/issue-30-20251106-2037 • Create PR ➔

---

Investigation Complete ✓

I've identified and fixed the root cause of this issue. When connecting via named pipes using .\SQLSERVER, the FullSmoName property returns NP:.$SQLSERVER which contains a colon - an invalid character for Windows filenames.

Root Cause

The FullSmoName property in DbaInstanceParameter.cs prepends the protocol prefix (e.g., "NP:" or "TCP:") to the computer name. This is correct for SMO connections but causes issues when this value is used to generate backup filenames.

Solution Implemented

Added a new property FileNameFriendly to the DbaInstanceParameter class that returns a sanitized version of the instance name suitable for use in filenames:

• Replaces colons (:) with underscores (_)
• Replaces backslashes (\) with underscores (_)
• Replaces commas (,) with underscores (_)

This allows the main dbatools repository's Backup-DbaDbMasterKey function to use FileNameFriendly instead of FullSmoName when generating backup filenames.

Changes Made

1. DbaInstanceParameter.cs:121-137 - Added FileNameFriendly property
2. DbaInstanceParamaterTest.cs:97, 119 - Updated existing tests to verify FileNameFriendly
3. DbaInstanceParamaterTest.cs:244-276 - Added comprehensive test TestFileNameFriendly covering various scenarios

Example Usage

var instance = new DbaInstanceParameter(@".\SQLSERVER");
// FullSmoName:       "NP:.$SQLSERVER"  (contains invalid ':' for filenames)
// FileNameFriendly:  "NP_._SQLSERVER"  (safe for use in filenames)

Next Steps

This fix is part 1 of the solution. The main dbatools repository will need to be updated to use the FileNameFriendly property instead of FullSmoName when generating backup filenames in the Backup-DbaDbMasterKey function.

--- | Branch