Administering and Monitoring Analysis Services - SQL Server Analysis Services 2012 Cube Development - page 290

Backup and restore
Assuring data availability is the primary responsibility of each database administrator,
so collecting and validating SSAS database backups is essential for your job security.
You can only collect full database backups—Analysis Services does not support
backups at individual cube, measure group, or dimension level, nor does it support
incremental or differential backups. Hence, when building SSAS databases, it is im-
perative to consider which data sets need to be backed up together. For example, if
80 percent of user queries examine current data and only 20 percent check histor-
ical data, you could implement two databases—one for historical and the other for
recent data. If historical data changes rarely, you may only need to backup the his-
torical database once a month, whereas the database with daily changes should be
backed up more frequently. Much like other database platforms, when thinking about
your SSAS backup strategy, you should consider how much data loss is acceptable.
Some organizations consider Analysis Services backups optional because the under-
lying relational data source can be used to rebuild the MOLAP database. Although
this is a valid statement, rebuilding SSAS database structure and reprocessing all ob-
jects could take considerably longer than restoring it from a valid backup. In addition
to being one of the methods for assuring continuous data availability, backup and re-
store could also be used for scaling out SSAS implementations.
How to do it...
To collect Analysis Services database backup, perform the following steps:
1. Connect to the instance using SSMS, expand the Databases folder, right-
click on the database of interest, and choose Back Up . Doing so activates the
Backup Database - AdventureWorks_Sample dialog shown in the following
screenshot.
2. Supply the full path and name of the backup file. The file extension must be
.abf . If you do not explicitly specify the path, the backup file will be stored in
the backup directory as defined in the msmdsrv.ini configuration file.
3. Specify whether the backup should be compressed. Analysis Services initially
reserves more space than it actually requires for storing the backup and sub-
sequently compresses the file (if the Apply compression box is checked).
4. Specify whether the backup should be encrypted with a password. Use this
option judiciously; if you forget or misplace the password, the backup file is