# Script out XMLA for all databases

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?

-
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

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
$svr.Connect($serverName)
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 [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true)
$xw.Close() }$svr.Disconnect()


References :

-