Create a XMLA for SQL Analysis OLAP Database Backup

Leave a comment (0) Go to comments

What is Script to take OLAP Database backup manually ?

How to Backup Analysis Services Database ?

YES, we can create a XMLA script to backup OLAP database .

Backing up Analysis Services database, lets administrators save a particular state of a SQL Server Analysis
Services database and its objects.

We can take Analysis Services Database Backup in various ways

  1. Manually, graphically using SQL Server Management Studio
  2. Manually, using a XMLA command / script
  3. Schedule a Analysis services backup as SQL Server scheduled Job.

Here we are learning option Three, Scheduling a OLAP Database backup

STEP By STEP procedure for backing up Analysis Services Database using XMLA script

STEP 1 - Connect to Analysis Services Instance on which you want to take backup.

Create a XMLA for SQL Analysis OLAP Database Backup xmla code to backup olap database how to write xmla code to backup analysis services database


STEP 2 - Once you are connected to Analysis Services in SSMS, in Object Explorer, right-click the
OLAP database (DBATAG in my case), and then click Back Up.

Create a XMLA for SQL Analysis OLAP Database Backup xmla code to backup olap database how to write xmla code to backup analysis services database

STEP 3 - In Backup Window, General page, Choose Browse Option to specify the backup location path

Create a XMLA for SQL Analysis OLAP Database Backup xmla code to backup olap database how to write xmla code to backup analysis services database

STEP 4 - In Save File As Box, specify the location, where you want to create a backup file. Once specified, Click OK. Ensure that file directory exists and you have write permissions in that directory.

Create a XMLA for SQL Analysis OLAP Database Backup xmla code to backup olap database how to write xmla code to backup analysis services database

STEP 5 - Instead of clicking OK, Select the Script option as shown below

Create a XMLA for SQL Analysis OLAP Database Backup xmla code to backup olap database how to write xmla code to backup analysis services database

STEP 6 - Once you select "Script to New Query Window" option, SSMS will automatically create a XMLA code for this operation like below.

Create a XMLA for SQL Analysis OLAP Database Backup xmla code to backup olap database how to write xmla code to backup analysis services database

Now any DBA / Developer can use this XMLA code to take backup of specified database.

I have posted an another article, where we can also schedule a OLAP database backup using this XMLA code.

EOF - Create a XMLA for SQL Analysis OLAP Database Backup, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.