SQL Server – Backing up Analysis Services Database automatically – Part 1

Hi Friends,

Backing up an Analysis Services database is one of the easiest things you can do while managing your OLAP databases. It is indeed very straight forward. Log on to SSMS -> right click your OLAP db, select Backup, fill in the details and press OK. Job done. But there are many more things that I want to talk about.

First, apart from GIU, you can use XMLA script to backup your database. You really don’t need to be an XMLA expert to do that. When you are on the Backup dialog box, use the script command to generate the XMLA script. See below:

1_SQL_Server_Backing_up_an_Analysis_Services_Database_automatically_Part1

So, what did I do? I simply fired up the backup window from SSMS. I left the default options (you can change them if you want). And click on the down arrow on the Script button, and then clicked on “Script Action to a New Query Window”. This will generate the XMLA command in a new window as follows:

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

 
    Adventure Works DW 2008
  Adventure Works DW 2008.abf

You can execute the above command in SSMS. In case you do so, verify the backup file in the default Backup folder in C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP \Backup

I ran the above script and got the following output in SSMS results window:

schemas-microsoft-com:xml-analysis">
  

And the Message window output was as follows:

Executing the query ...
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

  
    Adventure Works DW 2008
   Adventure Works DW 2008.abf  Backup started. Execution complete

Verify the backup file in the default folder:

2SQL Server Backing up an Analysis Services Database automatically – Part 1

   

So the above was the first thing I wanted to talk about.

Second, you can schedule this script to run automatically using SSIS or using a SQL Agent job. Backups should be automated, obviously. Typically a best practice is that your backup script runs automatically after an incremental load and cube processing is done. As mentioned, you can do this using SSIS or a SQL Agent job.

Let us see how you can schedule this script using SQL Agent job.

1. Log on to SSMS, DB engine.

2. Make sure SQL Agent is running. If not, start it.

3. Expand SQL Agent. Expand Job.

4. Right click Job, New Job.

5. Specify the name of the Job

6. Click on Step on the left side

7. Click on New at the bottom of the dialog box to add a new step.

8. Specify a step name

9. Change the type to SQL Server Analysis Services command

10. Specify the server name on which the command is going to run.

10. Copy/Paste the XMLA command that you generated in the previous steps.

11. The output should look like this:

3SQL Server Backing up an Analysis Services Database automatically – Part 1

12. Click on OK.

13. You can add a schedule if you want so. Or the job can be fired by your SSIS package.

14. For testing purposes, let us click OK and execute the job manually.

15. So click OK to save the job.

16. Right click the job, click on Start Job at step…

4SQL Server Backing up an Analysis Services Database automatically – Part 1

17. Once done, the output should be as follows:

5SQL Server Backing up an Analysis Services Database automatically – Part 1

18. Verify the backup file in the default folder:

So, in this post I demonstrated a few things about backing up your OLAP db using scripts and SQL Agent job. In my next post, I shall show how you can achieve this using SSIS package.

 

 

   

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 →

5 Comments on “SQL Server – Backing up Analysis Services Database automatically – Part 1”

  1. Amit Sir you write in very systematic manner, thankyou

    i know t-sql and basic sql, but want to learn BI, please tellme how to start from begining.

  2. Hi Amit,
    Is it possible to perform SSAS database backups remotely…i.e., configure one backup job for all SSAS instances..?

    1. Yes, you can call it from any remote server as long as it can talk with the AS server(s).
      All you would need to do is the following steps:

      10. Specify the server name on which the command is going to run.
      10. Copy/Paste the XMLA command that you generated in the previous steps.

Leave a Reply

Your email address will not be published.