Schedule Analysis Services Database Back Up

Leave a comment (0) Go to comments

Can we schedule a Analysis Service (OLAP)Database backup ?

How to Backup Analysis Services Database ?

YES, we can schedule a OLAP database backup.

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.

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

Schedule Analysis Services Database Back Up


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.

Schedule Analysis Services Database Back Up

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

Schedule Analysis Services Database Back Up

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.

Schedule Analysis Services Database Back Up

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

Schedule Analysis Services Database Back Up

STEP 6 - Once you select "Script to New Query Window" option, SSMS will automatically create a XMLA code for this operation like below. Select this Code XMLA command and Copy on clipboard.

Schedule Analysis Services Database Back Up

STEP 7 - Now we need to schedule this XMLA command for automate / schedule backups. So connect to SQL Server Agent which is part Database Engine using SSMS as shown below.

Schedule Analysis Services Database Back Up

 STEP 8 - In SSMS, Database Engine, go to SQL Server Agent then right click on Jobs container and select New Job

Schedule Analysis Services Database Back Up

STEP 9 - Create a New, specify Job name and then go to STEPS page,

Step 9 A – Create a Job

Schedule Analysis Services Database Back Up

Step 9 B – Create a Step in in Steps page of Job properties

Schedule Analysis Services Database Back Up

Step 9 C – In New Job Step Page, specify

  • Step Name
  • In Type : SQL Server Analysis Services Command
  • Run as : SQL Server Agent Service Account
  • Server Name : where this script will run, in this example, we have analysis services and agent on the same machine.
  • In Command section, paste the XMLA code which we generated in STEP 6
  • Click OK

Schedule Analysis Services Database Back Up

Step 9 D – Create a schedule for this Job and Click on New Button

Schedule Analysis Services Database Back Up

Step 9 E – Create a Step Schedule in Schedules page of Job properties by and specify in your schedule options like

    • Schedule Name
    • Frequency
    • Stat Time to run this job
    • Click OK to exit the Schedules page of Job properties
    • Click OK to exit the Job properties.

Schedule Analysis Services Database Back Up

Step 9 F - Verify Job , Verify the SQL Server Job , by right click and say start, which will create a backup of your OLAP database on specified time.

Note : -Currently daily your backup file will be overwritten, to prevent this, build another steps in job to rename the existing file.

STEP 10 -

Verify Job , Verify the SQL Server Job , by right click and say start, which will create a backup of your OLAP database on specified time.

Note : -Currently daily your backup file will be overwritten, to prevent this, build another steps in job to rename the existing file.

Once the backup is complete, you can verity the backup file in specified folder, you can see a file with extension .abf (Analysis backup file).

Schedule Analysis Services Database Back Up

Note : there is no option available for verification like relation engine services.

EOF - Schedule Analysis Services Database Back Up, 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.