SSAS 2008 – automating Analysis services backup

Hello everybody,

I believe many of you know or already use some of this methods, but I believe it is easier to have the options together.

Any additional suggestion /option/idea  is welcomed:).
For disaster recovery it is  very important to have backups for SSASdatabases . In order to automate the process there are several options available (that came in my mind):

-using Powershell

-using SQL Agent

-using SSIS tasks

-using ASCMD

Before discussing the available tools let’s review first the XMLA commands and options for backup and restore:

Backup :




 Practical  examples using Power Shell on the blog of Olivier Pieri :


 More  information are available in the documentation for SQL 2012– Analysis Services
powershell which also applies to SQL 2008 R2 – backup/restore



“Let’s take a look at the automation provided to you through SQL Server Agent jobs.

To manage a backup using SQL Server Agent jobs:

    1. Right-click the Jobs node under SQL Server Agent in the SQL Server Management Studio and launch the creation of the new job.

    2. Name the job in the Name text box.

    3. Click on the Steps node in the left pane, then click on New to create a new step in your job.

    4. Name the step and choose the step type SQL Server Analysis Services Command.

    5. Provide the name of the Analysis Services server on which you want to run
    this command. Then, cut and paste the Backup command you will be running into
    the Command window:

    6. Click OK in the New Job Step dialog box and then OK in the New
    Job dialog box.

    7. Go back to SQL Management Studio and you will find a new node for the job
    you just created.

    8. Right-click the new node and start the job.

    You should see a progress dialog box and then see your backup created.

    From  this point, you can use the full power of SQL Server Agent to manage the job.

    For example, you can add another step to copy the database file into a secure location and so on.

    SQL Server Agent also provides you with the ability to look at the history for executing jobs and many other exciting abilities.

    After you have learned all of the options of the Backup command, chosen the right
    backup strategy, and made sure that your automation scripts are functioning,
    you should feel a little more confident with your ability to recover from a

    You can find more details in the article below:


    SSIS :

    You can use SSIS taks to automate the backup/restore operations using  Analysis Services Execute DDL Task.



    The ascmd command-line utility enables a database administrator to execute an XMLA script,

     MDX query, or DMX statement against an instance of Microsoft SQL Server
    2005 Analysis Services (SSAS). This command-line utility contains functionality
    for Analysis Services that resembles the sqlcmd utility included with
    SQL Server 2005. For more information, see the topic sqlcmd Utility
    in SQL Server 2005. The execution results of the script, query, or statement
    can be stored in a file together with relevant SQL Server Profiler trace
    information. The default install location for the ascmd command-line
    utility is as follows:

    Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\ascmd

    Moreinformation about the ascmd parameters  you can find below:


    You can download the tool using the link below(you
    need to compile it first):


    Hope this helps!


    Comments (3)

    1. DURGA says:

      Here are my requirements :

      1) I want configure a single job which takes backups of multiple SSAS cubes. How do we separate the XMLA statements ( Like go in SQL Server Query )

      2) I want to maintain two latest backups of cubes. How can I achieve it ? ( I don't see any option to avail this )

    2. Mr Cool says:

      Thanks a lot dude. It was very helpful. Keep posting…!

    Skip to main content