Analysis Services databases should be backed up at regular intervals like any other database. Here are the basics.
Using the GUI: Connect to the SSAS service in SQL Server management studio. Right click on the database and select backup.
The Backup dialog presents a number of options.
Allow file overwrite – this option lets an existing file be overwritten.
Apply compression – this is the default and allows the backup to be compressed. This will slightly increase backup time and is unlikely to have a large effect on MOLAP databases.
Encrypt backup file – specify a password and secure sensitive business data.
Backup remote partitions – if some of the cube partitions are located on another server they will be listed here.
The Backup file name and browse button allow you to name the backup file and path. The folders that appear in the browse dialog are defined in the advanced server propertyAllowedBrowsingFolders. This is a pipe (|) separated list of local folder paths. Adding a folder to this list also gives access to all of its subfolders.
Using XMLA command scripts: SSAS backups can be scripted using XMLA (XML for Analysis Services).
Here is a backup taken to the default backup location (see the BackupDir server property), with overwrite allowed, compression on (the default) and using a password to encrypt the file:
The following backup is taken to a non-default location. The location does not need to be listed in the AllowedBrowsingFolders advanced server property and network UNC paths can also be used here. The backup will not be compressed and if the file already exists it will not be overwritten.
These XMLA scripts can be added to a SQL Agent job and scheduled as required.
