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 : https://technet.microsoft.com/en-us/library/ms186622.aspx
Restore: https://technet.microsoft.com/en-us/library/ms187189.aspx
POWERSHELL:
Practical examples using Power Shell on the blog of Olivier Pieri :
https://blogs.msdn.com/b/olivier_pieri/archive/2011/01/26/analysis-services-and-powershell.aspx
More information are available in the documentation for SQL 2012– Analysis Services
powershell which also applies to SQL 2008 R2 – backup/restore
https://technet.microsoft.com/en-us/library/hh213141(SQL.110).aspx
SQL AGENT:
“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
disaster.
You can find more details in the article below:
https://msdn.microsoft.com/en-us/library/cc917611.aspx
SSIS :
You can use SSIS taks to automate the backup/restore operations using Analysis Services Execute DDL Task.
ASCMD:
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:
<system_drive>\Program
Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\ascmd
Moreinformation about the ascmd parameters you can find below:
https://msdn.microsoft.com/en-us/library/ms365187(v=sql.90).aspx
You can download the tool using the link below(you
need to compile it first):
https://msftasprodsamples.codeplex.com/
Hope this helps!
Alex