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
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
