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.

msmdarch.exe

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

Comments on this post

# re: Backing up Analysis Services 2000 Databases

Requesting Gravatar...
Hi Darren, how are you?


My name is Vinicius Canto, Admin Frameworks/PowerShell MVP from Brazil.

Do you know if this procedure can be used in SQL Server Analysis Services 2005?

Thank you in advance,


Vinicius
Left by Vinicius Canto [MVP] on Oct 10, 2008 8:53 PM

# re: Backing up Analysis Services 2000 Databases

Requesting Gravatar...
It depends which procedure you are talking about. The msmdarch utility does not work with SSAS 2005.

However you could use the ascmd sample (which is up on codeplex) to send an XMLA command to SSAS to do the backup or you could use something like the following PowerShell:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
$svr = new-Object([Microsoft.AnalysisServices.Server])
$svr.Connect("Localhost")
$DatabaseID = "Adventure Works DW"
$db = $svr.Databases.Item($DatabaseID)
$db.Backup("c:\temp\AdventureWorksDW.abf")
$svr.Disconnect()

The concept of backing up the data folder is still valid. The following article has information on all the available backup strategies for SSAS 2005. http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Left by Darren Gosbell on Oct 11, 2008 7:41 AM
comments powered by Disqus