Team blog of the Microsoft EMEA support team for Analysis Services
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 SQL Agent
-using SSIS tasks
Before discussing the available tools let’s review first the XMLA commands and options for backup and restore:
Backup : http://technet.microsoft.com/en-us/library/ms186622.aspx
Practical examples using Power Shell on the blog of Olivier Pieri :
More information are available in the documentation for SQL 2012– Analysis Servicespowershell 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 runthis command. Then, cut and paste the Backup command you will be running intothe Command window:
6. Click OK in the New Job Step dialog box and then OK in the NewJob dialog box.
7. Go back to SQL Management Studio and you will find a new node for the jobyou 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 rightbackup strategy, and made sure that your automation scripts are functioning,you should feel a little more confident with your ability to recover from adisaster.
You can find more details in the article below:
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 Server2005 Analysis Services (SSAS). This command-line utility contains functionalityfor Analysis Services that resembles the sqlcmd utility included withSQL Server 2005. For more information, see the topic sqlcmd Utilityin SQL Server 2005. The execution results of the script, query, or statementcan be stored in a file together with relevant SQL Server Profiler traceinformation. The default install location for the ascmd command-lineutility is as follows:
<system_drive>\ProgramFiles\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(youneed to compile it first):
Hope this helps!
You can try this software www.sqlserverbooster.com
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 )