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

About these ads
This entry was posted in Disaster Recovery, SSAS. Bookmark the permalink.

One Response to Backups in SQL Server Analysis Services

  1. Pingback: Restores in SQL Server Analysis Services | ctrl-alt-geek

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s