Partager via

SSAS 2008 – automating Analysis services backup

  • Article

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

Comments

  • Anonymous
    August 29, 2014
    You can try this software www.sqlserverbooster.com

  • Anonymous
    March 18, 2015
    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 )
  • Anonymous
    August 03, 2015
    Thanks a lot dude. It was very helpful. Keep posting...!