How to delete previous database snapshot using regex?

Question

We are restoring a database called Cube1 as "snapshots" using below command.

$CUBE = "$CUBE-Snapshot-$dateStamp"
Restore-ASDatabase -Server $Target_Server -RestoreFile $BFile -Name $CUBE -Security:$SecurityChoice -AllowOverwrite -ErrorAction Stop

However, this is supposed to run weekly indefinitely and our server memory capacity will be diminished leading to performance issues eventually, which we don't want.

Therefore, I'd like to delete existing snapshots, so ultimate result would be something like this (1 snapshot only weekly):

I tried the following:

Import-Module sqlserver
$AnalysisServer = New-Object Microsoft.AnalysisServices.Server  
$AnalysisServer.connect("$Target_Server")
$AnalysisServer.Databases["*$CUBE-Snapshot*"].Drop()

and the drop operation failed.

You cannot call a method on a null-valued expression.

When specifying manually the snapshot name:

$AnalysisServer.Databases["Cube1-Snapshot-05-30-2021-0314PM"].Drop()

The drop operation succeeds.

I suppose I need to use some sort of regex then since wildcards didn't seem to work for the name, so how do I accomplish that?

Could you just capture the names in a variable and iterate through each one dropping then with a foreach loop? — Abraham Zinala, May 30 '21 at 21:47
@AbrahamZinala i could but i would still need to wildcard the DB names anyways so that im only targeting cubes with "snapshot" in the name...so im back to square 1 — Cataster, May 30 '21 at 21:48

mklement0 · Accepted Answer · 2022-03-10T23:25:17.633

Using [...] to index into the .DataBases collections corresponds to the parameterized .Item[] property, which supports (a) targeting a database by numerical index or (b) by verbatim name - using patterns to match database names is not supported.

Therefore, you need to filter the database collection explicitly, which you can do with the .Where() array method, combined with member-access enumeration, which allows you to call the .Drop() method on every matching database:

$AnalysisServer.Databases.Where({ $_.Name -like "*$CUBE-Snapshot*" }).Drop()

Note: If no databases match, the above will generate a statement-terminating error; to avoid that, use a two-step approach:

$matchingDbs = $AnalysisServer.Databases.Where({ $_.Name -like "*$CUBE-Snapshot*" })
if ($matchingDbs) { $matchingDbs.Drop() }

To delete all snapshots except the most recent one, based on the naming convention shown in the question:

$matchingDbs = $AnalysisServer.Databases.Where({ $_.Name -like "*$CUBE-Snapshot*" })
if ($matchingDbs.Count -ge 2) { 
  ($matchingDbs | Sort-Object Name -Descending | Select-Object -Skip 1).Drop() 
}

Thank you very much, that works :) ! I adjusted the logic a bit to accomodate for customized retention, `if ($matchingDbs.Count -ge $SnapshotRetentionFrequency) { ($matchingDbs | Sort-Object Name -Descending | Select-Object -Skip ($SnapshotRetentionFrequency - 1)).Drop() }` — Cataster, May 31 '21 at 05:42

How to delete previous database snapshot using regex?

1 Answers1