Blog Post

Backups in SQL Server Analysis Services

,

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.

image

The Backup dialog presents a number of options.

image

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 property AllowedBrowsingFolders. This is a pipe (|) separated list of local folder paths. Adding a folder to this list also gives access to all of its subfolders.

image

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.

image

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating