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.

3 thoughts on “PowerShell: Backup All SSAS Databases To A Network Share

  1. Thanks for this! It worked beautifully once I sorted out that $sqlInstance = ‘MyHost\MyInst’ needed to have the MyInst portion be ‘Default’ (specific to my box)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s