Backup Analysis Services Database Using PowerShell

We can use PowerShell to backup Analysis Services (AS) multidimensional or tabular database. This is great because it offers us more flexibility in automating the AS database backup process. Typically, we might have multiple Analysis Service databases within an Analysis Services instance. As a good Database Administrator responsible for the Analysis Services, we want to backup all those Analysis Services databases.

PowerShell Script: List SQL Server Analysis Service Role Permission By Database and Cube

A while ago, I wrote a couple of blog entries in regards to using PowerShell scripts in order to list roles within the database and their members. The script can be found here and here.

Let’s try to expand on those scripts further.

List SSAS User Roles Using PowerShell (Part 2)

A while ago, I have a blog post in regards to using PowerShell to list user roles and also their members. The blog post can be found here. I’ve gotten a few questions about expanding the PowerShell script to:

1. List user roles and their members for all databases within a particular SQL Server Analysis Services (SSAS) server.
2. Output the result into one table

Here’s a PowerShell script that can be use to accomplish those goals

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# SSAS server name variable
$SSASServerName = "ServerName"

# Try to connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Object to store the result
$Result = @()

# Get the SSAS databases and loop thru each of them
foreach ($DB in $SSASServer.Databases)
{
    # Get the SSAS database
    $SSASDatabase = $SSASServer.Databases.Item($DB.name)

    # Get the roles available within the SSAS database and loop thru each of them
    foreach ($Role in $SSASDatabase.Roles)
    {
        # Get the members within the role and loop thru each one of them
        foreach ($UserName in $Role.Members)
        {
            # Create a new object that would store the database name, role name and member user name
            $ItemResult = New-Object System.Object
            $ItemResult | Add-Member -type NoteProperty -name DatabaseName -value $DB.Name
            $ItemResult | Add-Member -type NoteProperty -name RoleName -value $Role.Name
            $ItemResult | Add-Member -type NoteProperty -name UserName -value $UserName.Name

            # Put the item result and append it to the result object
            $Result +=$ItemResult
        }
    }
}

$Result | Select DatabaseName, RoleName, UserName | format-table -auto -wrap | Out-String

List SQL Server Analysis Services Database Properties Using PowerShell

If you have SQL Server Analysis Services (SSAS) and want to list the SSAS databases using PowerShell, here’s a PowerShell script that can do that:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# Variable for your SSAS server name
$SSASServerName = "ServerName"

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Try to list the SSAS database Name
$SSASServer.Databases | Select Name

It turns out that you can do much more than listing your SSAS database name with this. You can get the SSAS database collation, compatibility level, created date, last processed, etc. To get the full list of properties that are available to you, try to run the following PowerShell script:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# Variable for your SSAS server name
$SSASServerName = "ServerName"

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Try to list the SSAS database properties that are available to you
$SSASServer.Databases | Get-Member -MemberType property | FT -wrap | Out-String

So now, if I want to get a list of SSAS database name, collation, compatibility level, created data and last processed date, I can run the following PowerShell script:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

# Variable for your SSAS server name
$SSASServerName = "ServerName"

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)

# Try to list the SSAS Database Name, Collation, Compatibility Level, Created Time, Estimated Size, Last Processed Date  
$SSASServer.Databases | Select Name, Collation, CompatibilityLevel, CreatedTimeStamp, EstimatedSize, LastProcessed | FT -wrap | Out-String

Related Blog Post:

List SSAS User Roles Using PowerShell

List SSAS User Roles Using PowerShell

Here’s the PowerShell script that can be use to list users and their roles in SQL Server Analysis Service (SSAS) database using PowerShell and Analysis Management Object (AMO):

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"

$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles | Select Name, Members

Notes (25th Apr 2013): Made a correction to one of the variable name on the code. On line 8, instead of using $SSASServer, it should use $SSASDB.