SQL Server: Scheduling OLAP Database backup using Integration Services – Part II

Hi Friends,

In my previous post, I showed how you can backup your Analysis Services database using SQL Agent job and XMLA.

In this post, I shall show you how you can use SSIS package to achieve the same. So, your XMLA for backup is as follows:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

 <Object>
   <DatabaseID>Adventure Works DW 2008</DatabaseID>
 </Object>
 <File>Adventure Works DW 2008.abf</File>
</Backup>

We shall create a SSIS package and use the Analysis Services DDL execute task to

  1. Start BIDS (Business Intelligence Development Studio) and create a new Integration Services Project (or) open any existing project and add a new package.
  2. You get the default Package.dtsx
  3. While you are in Control Flow, from the toolbox, drag Analysis Services Execute DDL Task.

1_SQL_Server_Scheduling_OLAP_Database_backup_using_Integration_Services_PartII

   
  1. You need to configure the task.
  2. Double click the task to configure it.
  3. In the Editor, click on DDL.
  4. In the connection, set up the connect to OLAP database that you want to backup
  5. In source type, select Direct Input.
  6. In the SourceDirect section, paste the above XMLA command.
  7. This is how the editor should look like:

2_SQL_Server_Scheduling_OLAP_Database_backup_using_Integration_Services_PartII

  1. Click OK
  2. The simple package is ready. You can execute it manually to test it. And then you can schedule the package using SQL Agent job.
  3. The backup will be taken in default OLAP backup folder.
  4. In case, you want to overwrite existing file, modify the XMLA command as follows:
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

 <Object>
   <DatabaseID>Adventure Works DW 2008</DatabaseID>
 </Object>
 <File>Adventure Works DW 2008.abf</File>
 <AllowOverwrite>true</AllowOverwrite>
</Backup>

That’s it. You are done.

Hope you enjoyed reading. I have also uploaded the sample SSIS solution in the Files section.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server: Scheduling OLAP Database backup using Integration Services – Part II”

  1. Hi Amit,

    A very nice post and thanks for sharing. In my organisation we have a backup server to maintain all the backups. Can this task help me take backup on a network drive also?

    or is synchronising the database, the only option to maintain ssas Db backup on a remote server?

    Regards,

    Mahesh S

Leave a Reply

Your email address will not be published.