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:

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:

And the Message window output was as follows:

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.