Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have.

How do I auto-generate XMLA scripts for all the databases?

share|improve this question
How were they deployed in the first place? If they were deployed through an SSAS project, to me at least, that seems like a backup already. Assuming the project files are in source control, which is backed up regularly. – Jon Seigel Oct 7 '13 at 16:21
Yes, some of them are in Source Control, but I want a snapshot of the database as is on Prod (with the new partitions, for example). – Raj More Oct 7 '13 at 16:33
up vote 3 down vote accepted

You can do it using powershell.

$serverName = "servername\instanceName" 
$outputFolder = "D:\data\"

## load the AMO and XML assemblies into the current runspace 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null 
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null 
$dateStamp = (get-Date).ToString("yyyyMMdd")

## connect to the server 
$svr = new-Object Microsoft.AnalysisServices.Server 
foreach ($db in $svr.Databases) 
    write-Host "Scripting: " $db.Name 
    $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8) 
    $xw.Formatting = [System.Xml.Formatting]::Indented 

References :

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.