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

Backing up an 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:

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:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008</DatabaseID>
  </Object>
  <File>Adventure Works DW 2008.abf</File>
</Backup>

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:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" />
</return>

And the Message window output was as follows:

Executing the query ...
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008</DatabaseID>
  </Object>
  <File>Adventure Works DW 2008.abf</File>
</Backup>
Backup started.
Execution complete

Verify the backup file in the default folder:

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:

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…

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

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.

If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks

Regards
@A_Bansal
@SQLServerGeeks
www.amitbansal.net