Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

The error is:
WARNING: [10:45:43][Get-DbaBackupInformation] Failure on XXXX | A positional parameter cannot be found that accepts argument 's3://<bucket>/folder1/folder2/) should be a file, not a folder.

or

When also passing the -MaintenanceSolutionBackup switch
WARNING: [10:45:43][Get-XpDirTreeRestoreFile] SqlInstace XXXX cannot access s3://<bucket>/folder1/folder2/FULL/

Steps to Reproduce

Restore-DbaDatabase -SqlInstance sql2022 -Path s3://s3.us-west-2.amazonaws.com/mybucket/backups/ -StorageCredential MyS3Credential

or

Restore-DbaDatabase -SqlInstance sql2022 -Path s3://s3.us-west-2.amazonaws.com/mybucket/backups/db1 -StorageCredential MyS3Credential -MaintenanceSolutionBackup

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

2.7.23

Other details or mentions

dbatools uses

1. sys.dm_os_enumerate_filesystem - Works for local/network paths only (SQL 2017+)
2. xp_dirtree - Works for local/network paths only (SQL 2000+ and until SQL 2017)

This can't access S3 buckets.

SQL Server limitation accessing S3

SQL Server doesn't have a native T-SQL function to list S3 objects, even though SQL Server 2022+ supports S3 for BACKUP/RESTORE operations. The S3 support is limited to reading/writing specific files, not directory enumeration.

Workaround

If we pass a list of paths to the files, SQL Server is able to read them and do the restore.

That said, we can leverage AWS PowerShell commands to get a list of files from a folder.
We can use Get-S3Object to build the needed full URL for all files we want to pass to the restore command.

# Restore from S3 storage folder (SQL Server 2022+)
# First, enumerate S3 bucket contents using AWS PowerShell module - You need to be authenticated!
$s3Files = Get-S3Object -BucketName "mybucket" -KeyPrefix "backups/AdventureWorks/" -Region "us-west-2"
$backupPaths = $s3Files | Where-Object { $_.Key -match '\.(bak|trn|dif)$' } | ForEach-Object { "s3://mybucket.s3.us-west-2.amazonaws.com/$($_.Key)" }

# Then restore using the enumerated file paths
Restore-DbaDatabase -SqlInstance sql2022 -Path $backupPaths -StorageCredential MyS3Credential

dbatools improvements

More checks, examples, tests and documentation will be added to properly warn the user about this.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

PSVersion 7.5.4
PSEdition Core
GitCommitId 7.5.4
OS Microsoft Windows 10.0.26200
Platform Win32NT
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 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64) Mar 18 2024 12:02:14 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2022 Datacenter 10. e (Build 20348: ) (Hypervisor)

.NET Framework Version

.NET 9.0.10