Copy-DbaDatabase does not cleanup backup files

[pollusb]

Verified issue does not already exist?

Yes

What error did you receive?

No error message. Copy-DbaDatabase is suppose to delete backup files using 2 methods:

1. Using Remove-Item with caller credential
2. Using xp_delete_file using source SQL Engine credential

1245: Remove-Item $backupFile
...
1250: $sql = "EXEC master.sys.xp_delete_file 0,'$backupFile'"
1252: $null = SourceServer.Query($sql)

First method works well But second does not. Here's what I needed to ran afterward to make it work:

DECLARE @DeleteDate DATETIME = GETDATE();
EXEC master.dbo.xp_delete_file
@FileType = 0,
@FolderPath = N'G:\SQL\Replication',
@FileExtension = N'bak',
@Date = @DeleteDate,
@Subfolder = 0

Steps to Reproduce

You need to create a shared folder and give access to source SqlInstance service account only. You can keep read but should not have write permission! Then run a copy-dbadatabase using the shared path mentionned. The files will still be in the folder after execution.

Are you running the latest release?

Yes

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

PSVersion 5.1.17763.2931 PSEdition Desktop PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...} BuildVersion 10.0.17763.2931 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 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

.NET Framework Version

PSChildName Version

---

Client 4.7.03190 Full 4.7.03190 Client 4.0.0.0

[andreasjordan]

But that would maybe delete other backups as well, doesn't it?

[pollusb]

I hope not. xp_delete_file is undocumented, but we have the parameters names. My guess is we have to used them.

[andreasjordan]

The current code uses $backupFile to just delete a specific file. Your code does not. So your code would also delete other files in that directory. Or what am I missing?

[andreasjordan]

I can not reproduce the issue. Will close this now, but can reopen if needed.