PowerShell Get-ChildItem with An Array Of Wildcard strings

Question

I have a folder with a bunch of SQL Server .BAK files of several databases. The .BAK file names keep changing every day.

Of those, I'm only interested in 3-4 databases' latest BAK files for Restore-SqlDatabase.

This is what I have so far:

$databases = @('Orders', 'Items', 'Returns')
$BackupPath  = '\\BACKUPSERVER\BackupData\'
$latestBackupFile = Get-ChildItem $BackupPath -Attributes !Directory *$databases[0]* | Sort-Object -Descending -Property LastWriteTime | Select-Object -First 1
$latestBackupFile
#Restore-SqlDatabase -ServerInstance "my-vm" -Database "Orders" -BackupFile "\\BACKUPSERVER\BackupData\AODA2.Orders.2024-01-15.22-23-13.BAK" -ReplaceDatabase
#Restore-SqlDatabase -ServerInstance "my-vm" -Database "Items" -BackupFile "\\BACKUPSERVER\BackupData\MMMWW.Items.2024-01-15.22-23-13.BAK" -ReplaceDatabase

The Restore-SqlDatabase command is working when executed by itself; I've included in the above script for context.

*$databases[0]* - This doesn't result in a wild card string like *Orders* as I hoped for.

I'm not sure how to craft a ForEach loop for a) looping through the $databases array to get the wild card string like *Orders*, *Items*, *Returns* etc and b) looping through the latest .BAK file for each of those wild cards to construct the Restore-SqlDatabase command.

I'm new to PowerShell; How to do this? Thank you.

Answer 1

PowerShell has two distinct grammars, described in the documentation as "parsing modes":

• Argument mode:
  • applies to everything immediately following a command name, including your *$database[0]* argument
• Expression mode:
  • applies to mostly everything else, and works exactly as you expect: $databases[0] would be interpreted as an array index operation

When PowerShell encounters a so-called "bareword token" in argument mode, it automatically interprets it as the contents of an expandable string literal expression.

In other words, the argument *$databases[0]* is interpreted the same as if you'd written "*$databases[0]*".

In an expandable string literal, only simple variable expressions are recognized and expanded - so PowerShell tries to evaluate $databases on its own without considering [0].

You can escape the string interpolation rules by wrapping the expression with the subexpression operator $():

"*$($databases[0])*"

It's good to know, but you don't really need it here - you need a loop!

I'm not sure how to craft a ForEach loop

The foreach loop statement in PowerShell is fairly straightforward:

foreach ($item in $collection) {
  # work with each $item here
}

Let's try that with your $databases array:

foreach ($databaseName in $databases) {
  # find the latest backup file containing the db name in its file name
  $latestBackupFile = Get-ChildItem $BackupPath -File -Filter "*$databaseName*.bak" | Sort-Object -Descending -Property LastWriteTime | Select-Object -First 1

  if ($latestBackupFile) {
    Restore-SqlDatabase -ServerInstance "my-vm" -Database $databaseName -BackupFile $latestBackupFile.FullName -ReplaceDatabase
  }
  else {
    Write-Warning "Unable to locate backup file for database $databaseName"
  }
}

Since we're no longer relying on an array indexer, $databaseName can now be expanded in the filter string as expected - on the first iteration the $databaseName variable will have the value "Orders", so the argument expression -Filter "*$databaseName*.bak" will result in the filter string *Orders*.bak

Lastly, if you need to pad the database name with characters that might otherwise be interpreted as part of the variable path, you can qualify the variable path with {}:

# underscore _ below would have been interpreted as part of the variable path expression `$databaseName_` if not for the curly brackets
... -Filter "*${databaseName}_*.bak"