Archive and Restore Databases Add-in

The Archive and Restore Databases Add-in enables you to use the OLAP Manager or msmdarch command to archive and restore Microsoft® SQL Server™ OLAP Services databases. Each database is archived to a single .cab file, which you can reserve for restoration requirements or migrate to other server computers. This file is called an “archive file.”

A database’s archive file stores the contents of the Microsoft Windows NT® directory named the same as the database. For example, the archive file for the sample FoodMart database stores the contents of the FoodMart directory. The default path of this directory is:

..\Program Files\OLAP Services\Data\FoodMart\

All OLAP Services databases reside in the Data directory. The path leading to the Data directory can be changed. (To determine the current path, in the OLAP Manager, right-click the server that contains the database, click Properties, and then see the Data folder box.)

The archive file also stores metadata for the database and its objects. The appropriate records from the OLAP Services repository are included in the archive file. By default the OLAP Services repository is:

..\Program Files\OLAP Services\Bin\msmdrep.mdb

However, the path of the repository can be changed at installation, and the repository can be migrated to a SQL Server database.


Caution When you restore a database, its file set (in the Data directory and its subdirectories) and its metadata are returned to their states at the time the archive file was created. Files that were created since then in these directories are deleted. Changes and additions since then to OLAP Services repository records for the database and its objects are removed.


When you restore a database:

If you restore a database containing ROLAP (relational OLAP) partitions and the restoration copies rather than replaces the original, archived partitions, the original ROLAP partitions and their copies will use the same aggregation tables. This situation may cause overwrite conflicts in the tables. For example, if you change and process an original partition, the aggregation tables may no longer be valid for its copy. To avoid this problem, immediately after restoration specify a unique aggregation prefix for each of the ROLAP partitions, and process the partitions whose aggregation prefixes you changed. This action creates different aggregation tables for each partition. To access the aggregation prefix for a partition, in the OLAP Manager tree view, right-click the partition, click Edit, advance to the final step of the Partition wizard, and then click Advanced.


Caution This add-in does not archive write-back tables, source data, or aggregations for ROLAP cubes and partitions. This data is required for ongoing, correct operation of OLAP Services. You must archive or back up this data with the backup software you ordinarily use because it is not contained in archive files created with this add-in.


Write-back tables are not stored in the archive file. Therefore, if you restore a write-enabled cube and its write-back table is not available, the cube must be processed before it can be used. After processing, the effects of the write-back data are absent from the cube’s data. For more information about write-back data and write-enabled cubes, see “Write-Enabled Cubes” in OLAP Services Books Online (msdssbol.chm).

The contents of the archive file vary according to the storage types of the cubes and partitions in the database. The following table indicates these variations.



Storage Type
Source Data Is Contained in Archive File Version of Source Data Usable by Cube/Partition Is Contained in Archive File Aggregations Are Contained in Archive File
MOLAP No Yes Yes
ROLAP No No No
HOLAP No No Yes

Restored MOLAP (multidimensional OLAP) cubes are usable even if their source data (that is, the tables in the data source used by the cube) is lost or unavailable. However, a restored MOLAP cube cannot be updated if its source data is lost permanently. Restored ROLAP and HOLAP (hybrid OLAP) cubes rely on the availability of their source data for correct operation. Restored ROLAP cubes also rely on the availability of their aggregation tables, which are stored with the source data.

(c) 1999 Microsoft Corporation. All Rights Reserved.