Archiving and Restoring Databases

Archiving and Restoring Databases

An archive file for a Microsoft® SQL Server™ 2000 Analysis Services database contains the contents of the directory named the same as the database. For example, the archive file for the FoodMart 2000 sample database stores the contents of the FoodMart 2000 directory. The default path of this directory is:

C:\Program Files\Microsoft Analysis Services\Data\FoodMart 2000

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

Important  The subdirectories of the Data directory store security files that control end users' access to objects on the Analysis server. These files are included in the archive files. For this reason, archive files must be secured against unauthorized access.

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

C:\Program Files\Microsoft Analysis 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 meta data 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 Analysis Services repository records for the database and its objects are removed.

When you restore a database:

• The files in the selected archive file are restored to the appropriate directories in the Data directory. If these directories already contain files with the same names as files in the archive file, the existing files are overwritten by the files in the archive file. If these directories contain files with different names, including new files that were created since the archive file was created, these files are deleted.
  
  
• The records in the Analysis Services repository that are associated with the database and its objects are replaced by the Analysis Services repository records in the selected archive file.

Sometimes restoration of a database containing relational OLAP (ROLAP) partitions copies rather than replaces the original, archived partitions. When this happens, 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 the copy of that partition. To avoid this problem, specify a unique aggregation prefix for each ROLAP partition immediately after restoration, and then 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 Analysis Manager tree pane, right-click the partition, click Edit, advance to the final step of the Partition Wizard, and then click Advanced.

Caution  Archiving a database does not archive writeback tables, source data, or aggregations for ROLAP partitions in that database. Writeback tables, source data, and aggregations for ROLAP partitions are required for ongoing, correct operation of Analysis 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 by Analysis Services.

Writeback tables are not stored in the archive file. Therefore, if you restore a write-enabled cube and its writeback table is not available, the cube must be processed before it can be used. After processing, the effects of the writeback data are absent from the cube's data. For more information about writeback data and write-enabled cubes, see Write-Enabled Cubes.

Data in remote partitions is not archived or restored with a database. After you restore a database that has a remote partition, you must process the remote partition. For more information about remote partitions, see Remote Partitions. To process a partition, see How to process a partition.

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

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