We are planning to take SSAS cube database backup since our backup tool does full file system backup in case of recovery, restoration is taking more time and hence as a first step we are started gathering the number of cubes,status and total size of cube databases .

SQLAS provider used for navigating the Analysis Management Object (AMO) hierarchy. You must import the SQLPS module before you can use the SQLAS provider and cmdlets. The SQLAS provider is an extension of the SQLServer provider. There are several ways to import the SQLPS module.

Please refer by earlier post to load SMO’s, if it’s not loaded.

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2013/05/01/powersql-list-sql-server-instance-configuration-details-and-sending-an-automated-email/

Create a file SSAS1.PS1 and paste the below content

********************************************************

Param($ServerName=”localhost”)

$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)

$server = New-Object Microsoft.AnalysisServices.Server

$server.connect($ServerName)

if ($server.name -eq $null) {

Write-Output (“Server ‘{0}’ not found” -f $ServerName)

break

}

$sum=0

foreach ($d in $server.Databases )

{

Write-Output ( “Database: {0}; Status: {1}; Size: {2}MB” -f $d.Name, $d.State, ($d.EstimatedSize/1024/1024).ToString(“#,##0″) )

$sum=$sum+$d.EstimatedSize/1024/1024

}

$SizeGB=$Sum/1024

write-host ‘Sum of Database = ‘$sum ‘ MB’

Write-host ‘Total Size of Cube Databases =’ $SizeGB ‘ GB’

***************************************************************

Open PowerShell – Type PowerShell in command prompt

PS C:\> .\SSAS1.PS1 <serverName>

Or Command Prompt

Powershell.exe c:\ssas1.ps1 <ServerName>

PS E:\> .\ssas.ps1 TEST

Database: Daily; Status: Processed; Size: 2,180MB

Database: Test; Status: Processed; Size: 49MB

Database: PP04; Status: Processed; Size: 57MB

Database: Trans; Status: Processed; Size: 1,613MB

Database: Complaint; Status: Processed; Size: 313MB

Database: PI; Status: Processed; Size: 8,687MB

Database: PI_Jey; Status: Processed; Size: 8,807MB

Database: PI_Trng; Status: Processed; Size: 8,474MB

Database: LG; Status: Processed; Size: 56MB

Database: VPP; Status: PartiallyProcessed; Size: 7MB

Database: Assign; Status: Processed; Size: 1,551MB

Database: Cubes; Status: Processed; Size: 3,044MB

Database: Cube; Status: Processed; Size: 3,048MB

Database: Tele; Status: Processed; Size: 2MB

Database: Pricing; Status: Processed; Size: 2,140MB

Database: DMDB; Status: Unprocessed; Size: 0MB

Database: LG cube; Status: Processed; Size: 62MB

Database: Manual; Status: PartiallyProcessed; Size: 2MB

Database: Tutorial; Status: Processed; Size: 10MB

Database: DW ; Status: Processed; Size: 32MB

Database: OLAP; Status: Processed; Size: 86MB

Database: SSAS; Status: Unprocessed; Size: 0MB
Sum of Database = 40220.1397724152 MB
Total Size of Cube Databases = 39.2774802464992 GB