Extracting SSAS MD Database(s) XMLA w/ PowerShell

  • Article

The easiest way of doing this, was to create a PowerShell script that extracts, saves and deploys the XMLA from the databases in question. So we would just be looping through a database collection on a number of servers, in order to get the required information.

Extract XMLA, then Save and Deploy

PowerShell

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL   $SourceServers = @( "<SOURCE SERVER LIST HERE>" ); #Source $TargetServer = "<TARGET SERVER HERE>"; #Target   cls;   $TargetServer.Connect();   ForEach( $srv in $SourceServers ) {     $server = New-Object Microsoft.AnalysisServices.Server     $server.connect($srv)     ForEach(  $database in $server.Databases ) {                  $stringbuilder = new-Object System.Text.StringBuilder         $stringwriter = new-Object System.IO.StringWriter($stringbuilder)         $xmlOut = New-Object System.Xml.XmlTextWriter($stringwriter)         $xmlOut.Formatting = [System.Xml.Formatting]::Indented           $ScriptObject = New-Object Microsoft.AnalysisServices.Scripter           $MSASObject=[Microsoft.AnalysisServices.MajorObject[]] @($database)           $ScriptObject.ScriptCreate($MSASObject,$xmlOut,$false)           $ServerPath = $server -replace "\\", "_"           $Path = "<INSERT DUMP LOCATION>" + ".xmla";                   $stringbuilder.ToString() |out-file -filepath $Path                  #$TargetServer.Execute(@StringBuilder.ToString());     }     }

Basically there are a few steps you need to do, in order to fit this script into your environment. Mind the list of sources, multiple servers can be added, although this has not been tested for name clashing on target box.

If you don’t need a physical file, for the archive, this step can obviously be skipped.

Finally, you may have to clear the database collection on the Target server, before running the script. This was done as a one off. This step is also disabled, commented out, in the script above. Less distruction on account of copy/paste.

Obviously if the cubes needs to be processed at the Target location, this needs to be added as well. We were just after the Metadata, hence no processing was needed.