Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

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


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...