4

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?

2
  • 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, 2013 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, 2013 at 16:33

1 Answer 1

4

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 :

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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