Restore-DbaDatabase

[corelevel]

Verified issue does not already exist?

Yes

What error did you receive?

WARNING: [19:02:40][Restore-DbaDatabase] No backups passed through. This could mean the SQL instance cannot see the referenced files, the file's headers could not be read or some other issue

Steps to Reproduce

$Instance = 'REDALERT'

$SrcDatabase = '2clone' $DstDatabase = '2clone_copy'

$Path = 'C:\Temp'

$BackupFileName = $SrcDatabase + '.bak' $BackupFullFileName = $Path + '\' + $BackupFileName

$Password = ConvertTo-SecureString 'cloner_password' -AsPlainText -Force $Credential = New-Object System.Management.Automation.PSCredential ('cloner', $Password)

Backup-DbaDatabase -SqlInstance $Instance -Path $Path -FilePath $BackupFileName -Database $SrcDatabase -SqlCredential $Credential -Type Full -CopyOnly -CompressBackup -Initialize -IgnoreFileChecks Restore-DbaDatabase -SqlInstance $Instance -Path $BackupFullFileName -Database $DstDatabase -SqlCredential $Credential -ReplaceDbNameInFile -WithReplace

Are you running the latest release?

Yes

Other details or mentions

Setup: [cloner] login is member of dbcreator server role [cloner] user is member of db_backupoperator database role for [2clone] database

Issue details: Backup works fine. Restore doesn't work. Here is the error: WARNING: [19:15:05][Restore-DbaDatabase] No backups passed through. This could mean the SQL instance cannot see the referenced files, the file's headers could not be read or some other issue

But if I run restore script using SSMS, it works without issues, using the same login Script also will work if I add the login to the sysadmin server role

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

PSVersion 5.1.19041.1320
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.1320
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 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64) Nov 6 2020 18:19:52 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19044: )

.NET Framework Version

4.8

[andreasjordan]

Might not solve your issue, but I would use these commands:

$backup = Backup-DbaDatabase -SqlInstance $Instance -Path $Path -Database $SrcDatabase -SqlCredential $Credential -Type Full -CopyOnly -CompressBackup -Initialize -IgnoreFileChecks
$backup | Restore-DbaDatabase -SqlInstance $Instance -Database $DstDatabase -SqlCredential $Credential -ReplaceDbNameInFile -WithReplace

This way Backup-DbaDatabase generates the filename and save it inside of the $backup object. Passing that to Restore-DbaDatabase will restore the exact backup.

[corelevel]

Yeap, that works for my case. But still not sure why it doesn't work without pipelining. Thanks!

[MattHubble]

It looks like $BackupFullFileName will be set to C:\Temp2clone.bak since $path doesn't have a trailing backslash, keeping Restore-DbaDatabase from finding the backup file.

[corelevel]

I was thinking the same when I checked pasted script. But backslash is there. For some reason, it doesn't become visible on Github.

[MattHubble]

I was just able to reproduce it with the \ added back to $Path.

DEBUG: 65868 | [15:46:16][Restore-DbaDatabase] Unverified input, full scans - C:\Temp\2clone.bak DEBUG: 20253 | [15:46:16][Get-DbaBackupInformation] Starting DEBUG: 20254 | [15:46:16][Get-DbaBackupInformation] Parameters bound: SqlInstance, SqlCredential, Path, DirectoryRecurse, MaintenanceSolution, IgnoreDiffBackup, IgnoreLogBackup, AzureCredential, NoXpDirRecurse DEBUG: 20303 | [15:46:16][Get-DbaBackupInformation] Testing a single file @{Length=18; FullName=C:\Temp\2clone.bak} DEBUG: 20307 | [15:46:17][Get-DbaBackupInformation] [INSTANCE] cannot 'see' file C:\Temp\2clone.bak WARNING: [15:46:17][Restore-DbaDatabase] No backups passed through. This could mean the SQL instance cannot see the referenced files, the file's headers could not be read or some other issue

It seems to be caused by Get-DbaBackupInformation calling Test-DbaPath which uses xp_fileexists.

From this very old Microsoft pdf SQL logins won't get results from it unless they're sysadmin

If the calling user is 'sa' this Stored Procedure is executed in the context of the SQL Server system account. In all other cases the Stored Procedure will be executed in the context of the calling user (i.e. the Stored Procedure will impersonate the user). This impersonation will fail for the case that a SQL login is used and an empty set will be returned.

[corelevel]

Yeah, probably you are right. The backup file was not visible when I ran the query under [cloner] login.

[andreasjordan]

Hi @corelevel - so can we close this issue or is there still a problem?

[andreasjordan]

I will close this now, but can reopen if needed.