Backing up Analysis Services 2000 Databases

There are a number of different methods available for backing up your Analysis Services 2000 database. I recently pulled together this vbscript for automating the calling of msmdarch.exe and thought I would post an article on the full range of backup/restore options.

Analysis Manager

This is the method that most people are aware of. It is fine for migrating databases, or for restoring databases. But for archiving/backing up databases you really want something that can be automated. If your database structure does not change much and does not take too long to build, maybe you could get away with manually archiving, but for most real world scenarios an automated backup is preferrable.


This is the command line tool supplied by Microsoft, it allows you to scedule a task that will archive the analysis services database. It is not very sophisticated, if you want to backup to unique, timestamp file names you need to some scripting of your own. I have done some of this within a DTS package or it can be done in a standalone VBScript file like the one I wrote here.

Manually backup the repository and data folder.

This technique is not as well known, but has been written up in some white papers (although I was at a loss to locate them). Due to limits with the .cab file format, if you have individual files within your data folder that are in excess of 2Gb, this is the only technique that you can use to backup your Analysis Services 2000 database. To backup your Olap data manually is a 2 step process.

  1. Back up the repository database
    Ideally you have migrated your repository to SQL Server so this step is easy, otherwise you need to backup the msmdrep.mdb file from the \bin folder under your Analysis Services install folder, making sure that no copies of Analysis Manager are open when you do so.
  2. Back up the olap data folder
    By default this is in \Program Files\Microsoft Analysis Services\Data , but you can alter this from the Server's properties in Analysis Manager, so you should double check there if you are unsure.

To restore a manual back up you should first stop the Analysis Services service and then restore the data before restarting the service.

Print | posted on Sunday, October 2, 2005 6:44 PM