Backing up an SSAS database is an important part of a data warehouse maintenance plan. The backup process is straightforward and can be done from SSMS, a DDL command, or AMO application. It’s important to understand what this process is doing (or not doing).
The backup feature of SSAS backups the database metadata and dimension data. It also backups partition data for partition using the MOLAP storage mode. MOLAP data is stored in SSAS so it can be be backed up. ROLAP partitions, however, are not included in an SSAS backup. This is because ROLAP partitions use the relational engine for data storage. Therefore, you need to backup the relational database too. Writeback partitions are also stored as ROLAP so writeback data will not be included in an SSAS backup. Again, you need to backup the relational database.
It might also be beneficial to backup the msmdsrv.ini file. This file contains the configuration information for the SSAS instance. Having a backup of this file will make restoring the server configurations much easier and much less manual.
Finally, the query log may be important to backup. The query log stores information on the queries executed on the server. This can be used for usage based optimization. The query log data is stored in a relational database. Back up the relational database if the query log data is important.
A few interesting points
- The backups can be compressed and compression is on by default.
- A backup will likely fail if the cube is processing.
- There’s no option for an incremental backup. A backup operation can only perform a full backup.