PowerShell: Backup All SSAS Databases To A Network Share

I am working on downgrading SQL Server Enterprise Edition (EE) s/w to Standard Edition (SE) where EE features are not used (to save money on licensing). We have a big list of these to go through. That required all SSAS databases to be backed-up to the network and restored after the edition downgrade. I will share the bigger script to help with the complete downgrade that includes SQL Server, SSIS, SSRS and SSAS at a later point in time.

For now, you can use the below snippet to backup all SSAS databases in an instance. Sorry, I am still using the SQLPS module instead of the newer SQLServer module but the code should be no different.

$backupFolder = '\\MyNetwork\MyShare\SSASBackups\'

$sqlInstance = 'MyHost\MyInst'
$computerName = $sqlInstance.Split('\')[0]
$instanceName = $sqlInstance.Split('\')[1]

#Import the SQLPS module (recommend the newer SQLServer module instead)
Import-Module SQLPS

#Get the list of AS databases
$asDBS = Get-ChildItem "SQLServer:\SQLAS\$computerName\$instanceName\Databases" |
SELECT -ExpandProperty Name

#Create the folder if it does not already exist!
New-Item -Path "$backupFolder" -ItemType Container -Force

#Loop through each DB and back it up locally!
ForEach($db in $asDBs)
{
    $backupFile = "C:\Temp\$db.abf"
    Write-Verbose "Backup SSAS database [$db] to [$backupFile]"

    #Backup with compression
    Backup-ASDatabase -BackupFile $backupFile -ApplyCompression -Server $sqlInstance -Name $db -AllowOverwrite -Confirm

    #Move the local backup to the network!
    Move-Item -LiteralPath "$backupFile" -Destination "$backupFolder"  -Force
}

That’s it! There is not much more to it. Just use the Restore-ASDatabase to do the reverse and restore the databases.