Add-DbaAgDatabase not working from inside a Linux Docker container due to it using Restore-DbaDatabase with ReuseSourceFolderStructure.

[fberlin]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [07:00:12][New-DbaDirectory] Failure | Error executing extended stored procedure: Invalid Parameter WARNING: [07:00:12][New-DbaDirectory] Failure | Error executing extended stored procedure: Invalid Parameter WARNING: [07:00:13][Add-DbaAgDatabase] Failed to restore database db-01 to replica SRV062\INST02. | Microsoft.Data.SqlClient.SqlError: The operating system returned the error '3(The system cannot find the path specified.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/app\db-01_data.mdf'. WARNING: [07:00:13][Add-DbaAgDatabase] Failed to restore database db-01.

Steps to Reproduce

Run the following command inside i Linux Docker container with dbatools installed (Should probably fail on any Linux client with Powershell and dbatools installed as well):

Add-DbaAgDatabase -SqlInstance "SRV061\INST02" -AvailabilityGroup d-ag-inst02 -Database "db-01" -SharedPath "\\server01\backup\inst02" -SeedingMode Manual

I'm using dbaTools from inside a Linux Docker container to automate database jobs. I have two SQL Servers set up as an availability group on two Window servers. When i run the Add-DbaAgDatabase command it fails with the error above.

One of the steps that Add-DbaAgDatabase does is to restore the database to the secondary SQL Server using the command Restore-DbaDatabase. The issue seems to be that ReuseSourceFolderStructure is hard-coded to true when calling Restore-DbaDatabase. This results in the strange looking path '/app\db-01_data.mdf'.

/app is the working directory inside my Docker container so I guess that Restore-DbDatabase tries to use a Linux path on a Windows machine.

You can reproduce the error by executing Restore-DbaDatabase directly inside the Docker container:

Restore-DbaDatabase -DatabaseName "db-01" -SqlInstance "SRV062\INST02" -Path \\server01\backup\inst02 -ReuseSourceFolderStructure

Fails with: WARNING: [07:16:47][New-DbaDirectory] Failure | Error executing extended stored procedure: Invalid Parameter

When running without -ReuseSourceFolderStructure it works as intended.

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

Major Minor Build Revision

---

2 1 5 -1

Other details or mentions

The hard-coded ReuseSourceFolderStructure is a result of issue https://github.com/dataplat/dbatools/issues/8192. One of the things discussed there was to add a parameter named AdvancedRestoreParams where you could set ReuseSourceFolderStructure. This was not implemented and ReuseSourceFolderStructure was hardcoded to true.

Client: Linux Docker container with Powershell and dbatools installed. Servers: Two SQL Servers set up as an availability group on two Windows Servers.

Dockerfile:

# Use the official PowerShell Core image as the base
FROM mcr.microsoft.com/powershell:latest AS production

# Install dbatools module
RUN pwsh -command "Install-Module -Name dbatools -Force -Scope AllUsers"

# Set the dbatools insecure connection configuration
RUN pwsh -command "Set-DbaToolsInsecureConnection"

# Create a directory inside the container to store the module
RUN mkdir /app

# Set the working directory inside the container
WORKDIR /app

ENTRYPOINT ["pwsh"]

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

PSVersion 7.4.0 PSEdition Core GitCommitId 7.4.0 OS Ubuntu 22.04.3 LTS
Platform Unix PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5021126) - 14.0.3460.9 (X64) Jan 25 2023 08:42:43 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

N/A

[andreasjordan]

This is not a problem with the AG command itself. It is not a problem with the parameters for backup and restore. It is a problem within the command Format-DbaBackupInformation which has to be used on the same os platform as the sql servers.

I think this is the relevant code:

$Pname = [System.Io.FileInfo]$_.PhysicalName
$RestoreDir = $Pname.DirectoryName

I don't have a quick fix for you. You can try to do the backup and restore part on your own before using Add-DbaAgDatabase. But you have to play with the parameters to not fall into the same problem.

[andreasjordan]

To add "cross platform support" for Format-DbaBackupInformation, we would have to eliminate [System.Io.FileInfo] and replace it by some code that takes the full path and returns the following properties (examples for $Pname = [System.Io.FileInfo]'D:\Some\Dirs\File.bak':

• directory name ($Pname.DirectoryName / "D:\Some\Dirs")
• base name ($Pname.BaseName.Split($PathSep)[-1] / "File")
• extention ($Pname.extension / ".bak")

Will play with that a bit...

[andreasjordan]

I think this will work:

$PathSep = '\'
$PhysicalName = 'D:\Some\Dirs\File.bak'
$lastPart = $PhysicalName.Split($PathSep)[-1]
$RestoreDir = $PhysicalName.Substring(0, $PhysicalName.Length - $lastPart.Length - 1)
$extension = '.' + $lastPart.Split('.')[-1]
$baseName = $lastPart.Substring(0, $lastPart.Length - $extension.Length)

But there might be a more elegant way to get the same result.

Let me ping some folks that should have a look at this: @Stuart-Moore @potatoqualitee @lowlydba

[fberlin]

Thanks for your help! I solved it by making a manual backup/recovery using Backup-DbaDatabase and Restore-DbaDatabase before running Add-DbaAgDatabase.

After a lot of fiddling with parameters it finally worked :-)