SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Perform and schedule SSAS Backups ?

Steps to configure schedule SSAS Cube \ OLAP database backups :-

1. For each SQL Server Analysis Services Environment. In order to create recovery strategy, we need to have backups of the metadata that describes the databases. SQL Agent jobs must be created to run backups remotely. Typically, one of the SQL Server Engine that are related to the SSAS environment is used to host the SQL Server Agent job.

2. We need to schedule job to run on predefined intervals. We can discuss the job schedule based on business/app team requirements.

3. You can also plan to move backups from disk to tape using Tape backup client like NetBackup, if used in environment.

4. We need separate step for each OLAP database and should be setup to continue to the next step(s) on failure, unless it’s the last step in the job, in that case it should be setup to either end successfully or with failure. The name should include the name of the database being backed up.

E.g. Backup DBNAME_SSAS

5. Each job step contents are as follows, replacing the DatabaseName, with the database id of the OLAP database requiring regular database backups.

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

<Object>

<DatabaseID>DatabaseName</DatabaseID>

</Object>

<File><Backup Location>\DatabaseName.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>

6. You can also have job email notification for job success or failure. Also, make sure SQL Job is owned by sa.

7. SQL Server Agent service account needs to have administrative permissions on the SSAS environment that will be backed up.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

MSSQLFUN

I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.

Comments

Leave a comment on the original post [mssqlfun.com, opens in a new window]

Loading comments...