Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 2 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 
$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 :

share|improve this answer
add comment

Your Answer

 
discard

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.