We need to take regular backup of SSAS database as we do with the SQL Server database to be able to restore it if something severely happens.
When we backup an SSAS Database, it is carried out by analysis services into one single operating system file with an extension of “.abf”, meaning analysis services backup file.
What is stored in a backup file depends on the storage mode of an analysis services cube/ partition. The below table shows how analysis services takes the backup operation
- Multidimensional OLAP (MOLAP) – Meta Data of the Cubes, All the detail data pulled while processing the cube and all the aggregations that are designed.
- Relational OLAP (ROLAP) – Backups up only the Structure/ Metadata of the cube.
- Hybrid OLAP (HOLAP) – Back up all the aggregations and Structure/ Metadata of the Cube.
The point to be noted over here is, when we backup or archive an SSAS Database, the underlying Data Source/ Data Warehouse/ Data Marts which are there in the relational database format are not backed up.
For taking a backup of an Analysis Services database, you should either be a member of a server role of SSAS instance or having administrator permission on the SSAS Database
Now, Let us see how to backup an SSAS Database
Open the Management Studio and Connect to Analysis Services.
Select the database that you want to backup and right click on it, now you will get a menu where you will have an option for “Back Up…”, select it
Now you have the main screen where you have the Database Name, the “.abf” file name and a few other options
There are three main options that you get here
- Allow file Overwrite – This option as the name suggests overwrites and existing file with the same name.
- Apply Compression – This option specifies if the backup that you are taking needs to be compressed to save space on disk.
- Encrypt Backup File – This option is kept for extra security to the backup file, you will need the password before the file can be unencrypted and restored on to an analysis services instance.
In this article we have looked at how to backup a SQL Server Analysis Services Database. If you have like it and want to get the articles (or) new post at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same.
if you have any comments, then the below form is for you, go ahead!!!
Comments on this entry are closed.
{ 2 comments }
Hey, that was a good article on the backup stuff, I have trid that, but I am not able to change the backup folder. any help on how to change the backup folder location, not exactly getting that.
Sorry Mark, had some issues with the blog and could not respond, yes by default you see only two folders, you need to go to analysis services properties and set the backup folder locations there, will write a post on that. Thanks.