Backup and Restore Cube at a time using SSIS Package.

Hello all,

Firstly, what I want to say is that ,I cannot stop myself to write this another blog of mine.

writing a blog and sharing some knowledge through the blog makes us really feels happy and also somewhat we also become addicted to it. 🙂

Also, this may be simply for one but for someone it will be helpful for those who don’t know how to do it.

May be there are a lot of method to perform this task and many solution’s.

While working I have to take a Backup of Cube from one database and Restore it into another database within a same machine.So,for this i usually do manually by right clicking the Backup and Restore it to another database but every time say development cube changes frequently and i have to take backup and then again restore it into production cube.

Doing manually seems little lazy,so i think of doing it dynamically or say by just a click so that.

For this I use a SSIS package where we can use the “Analysis Services Execute DDL Task ” from toolbox and by editing it, we can set/configure the values as:

Also, i forgot to mention that for the restore and backup XMLA script ,we have to right click on the database and click restore/backup and then click the script button so that it will generate the XMLA script for restore and backup to particular database.After generating the XMLA,we can directly put into the “Analysis Services Execute DDL Task” by configuring the “SourceType” :Direct input and SourceDirect : XMLA script (i.e of backup / Restore).

But here i use the file connection for pointing the XMLA script from particular location so that we can execute this for different database by only changing the XMLA file.

This may be simple but i think it may work out for someone .


Anil Maharjan

Leave a Reply