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 ( http://www.sqltechnet.com/2013/06/powershell-to-script-sql-database.html ), 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 http://geekswithblogs.net/darrengosbell/archive/2010/02/22/ssas-automating-the-scripting-of-an-ssas-database.aspx 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

2. REAPPLYAS.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.