Scheduling SSAS Backups in SQL Server Agent

Scheduling SSAS Backups in SQL Server Agent:

To schedule an SSAS backup, follow these steps:

Step1:

Connect to Analysis Services. Expand the Databases folder

Step2:

  Right click on Database which wishes to take backup and select backup

Step3: 

In the Backup Database dialog box, expand the Script drop-down list, and then select

Script Action To New query window (or press Ctrl+Shift+N). .

Script will be:

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

  <Object>

    <DatabaseID>cubessas</DatabaseID>

  </Object>

  <File>cubessas.abf</File>

  <AllowOverwrite>true</AllowOverwrite>

</Backup>

 DatabaseID and File will change according to DATABASES. Default value of File will be cube name with extension “.abf”. And backup of database will be taken in “C:\Program Files\Microsoft SQL Server\MSAS10.ADMIN123\OLAP\Backup”. If we need to change backup location, then change the value of File, with location where backup to be taken.

Example: We need to take  backup of cubessas.abf in C:\test_backup. Then File value will be <File>C:\test_backup\cubessas.abf</File>.

Step4:

 Connect to the SQL Server Database Engine instance where the job will run. Make sure

that the SQL Server Agent service is running. In Object Explorer, Expand the SQL

Server Agent node (it should be the last node in Object Explorer), Right click on Jobs and then

select New Job. In the New Job dialog box that appears, give the new job a name.

Step5:

•          Click the Steps in Select a page, and then click New to create a new step, give the new step name.

•         Expand the Type drop-down list, and then select SQL Server Analysis Services Command

as a step type.

•          Enter the name of the server that will execute the script—(local or Server name), for example.

•        Paste the backup script that you copied to the query window into the Command box, as

Figure shows.

 

•          Click OK to close the New Job Step dialog box.

Step6:

 

In the New Job dialog box, click the Schedules page to schedule the job execution

when you want it to run. If you want to run the job immediately, click OK to create

the job. Expand the Jobs folder under SQL Server Agent, right-click the job you just

created, and then click Start Job. SQL Server Agent will start the job and display its

progress in the Start Jobs dialog box.

Step7:

 

To view the job history log, right-click the job, and then click View History.