restore-dbadatabase under Linux PS Core and DestinationDirectory

Open Skyscraper76 opened this issue 2 years ago • 1 comments

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Hi there, I have a some sql-servers on windows and they creates backups to to cifs share. These backups should be restored for checking pruposes to a linux sql server instance. Why do we use a linux instance? The customer wants to evaluate sqlserver under linux in a not productiv way. Because the backups were created under windows, the backupinformation have the windows path in it. When a restore the backups to linux, the files are created in /var/opt/mssql/data/d:/mssql/..... This is a mix of linux and windows folder structure from the source instance. The parameters -UseDestinationDefaultDirectories and -DestinationDataDirectory '/var/opt/mssql/data' -DestinationLogDirectory '/var/opt/mssql/data' will change nothing. The files are now stored in the folder mix. I want to have them in /var/opt/mssql/data or in an other default directory

Steps to Reproduce

This script is executed on the linux box

$restInst = '<instance>' # Instance where the backups will be restored
$pathToBkp = '/mnt/cifs' # linux mount point to sql-server backups
$exclude = '*model*', '*msdb*', '*master*' # exclude these file patterns
$cred = get-credential # store sql user 

Set-DbatoolsInsecureConnection -SessionOnly # turn off TLS default

Get-ChildItem -Recurse -Include '*.bak' -Path $pathToBkp -Exclude $exclude  | where {$_.LastWriteTime -gt (Get-Date).AddDays(-1) -AND $_.FULLNAME -notlike '*~snapshot*' } | select Fullname | export-csv -Path baks.csv -Delimiter ';' # look in the mount point and get all bkps from yesterday and export them into csv file

Write-Host "Look in csv file and remove lines / backups, that should not be restored"
$null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown');
# Config

Import-Csv -Path ./baks.csv -Delimiter ';' | % {

    Restore-DbaDatabase -SqlInstance $restInst -SqlCredential $cred -Path $_.FullName -DestinationDataDirectory '/var/opt/mssql/data' -DestinationLogDirectory '/var/opt/mssql/data' -Verbose # restore all backups from csv  
}

Get-DbaDatabase -SqlCredential $cred -SqlInstance $restInst -ExcludeSystem | Remove-DbaDatabase # remove all user dbs

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

Get-Module dbatools | Select -ExpandProperty Version

Major Minor Build Revision

2 0 4 -1

Other details or mentions

No response

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), VS Code (terminal), VS Code (integrated terminal)

PowerShell Host Version

Name Value

PSVersion 7.3.8 PSEdition Core GitCommitId 7.3.8 OS Linux 5.4.0-164-generic #181-Ubuntu SMP Fri Sep 1 13:41:22 UTC 2023 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

.NET Framework Version

.NET 7.0.11

Oct 20 '23 09:10 Skyscraper76

Also coming up against this, I've tried all sorts of combinations of invoking Restore-DbaDatabase with and without Format-DbaBackupInformation

-FileMapping, -UseDestinationDefaultDirectories, -DestinationDataDirectory /var/opt/mssql/data - all fail.

Dec 15 '23 09:12 megakid