Thursday, 15 August 2013

PowerShell to Script SQL Analysis Service Database(XMLA Script) and ReApply on a different Server

Similar to my  post last month to script and reapply DB service database objects ( ), I was working on a project migrating Analysis services databases from SQL Server 2008 to SQL server 2012 and my client did not want to go the backup and restore route due to some changes to the base database schema structure. So we decided to script the AS database and re-apply it on the destination before we process the cubes. There was also a requirement to change the data sources after the source DB script was created as the server names has changed in the new SQL 2012 farm.
 I took the method mentioned in as my base for scripting and created a wrapper to script the databases and change the data source as per the server name mentioned in the script as below. 

1. ScriptASDB.ps1


I have used Invoke-ASCMD mehod to reapply the xmla scripts, invoke-ascmd method can be found in sqlps module of powershell and unfortunately this model comes only with SQL Server2012 client, so you need a SQL Server 2012 client installed to run this script but the script will execute perfectly for SQL 2008 AS, we only need the SQL 2012 client only for the invoke the command.

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.


  1. Hi, thank you for the script. I use it to export SSAS multidimensional models to xmla XML files. Works great for this.
    There is an issue when the SSAS is using Tabular Model which has been available since version 12 (compatibility level 1200) of SQL Server.
    The code is not able to extract the JSON model file, what is returned looks like the header of the model in XML.
    I have searched on how to perform the same operation for SSAS tabular models and could not find references or documentation for powershell.
    How can the same operation be performed for SSAS tabular models?

  2. It's very difficult to stumble upon the right essay service for your college needs. That's why reddit lovers prefer