Edit

Share via

Restoring Analysis Services and Supporting Databases

  • Article

There are two SQL Server Analysis Services databases that must be restored in a disaster recovery scenario:

  • BAM Analysis (BAMAnalysis)

  • Tracking Analysis Server (BizTalkAnalysisdb)

    The BAM SQL Server databases may be backed up as part of the Backup BizTalk Server job if BAM is enabled but not configured.

Note

The BAM Primary Import database is always backed up as part of the Backup BizTalk Server job because it participates in DTC transactions.

The following BAM databases will be part of the Backup BizTalk Server job if BAM is enabled but not configured:

  • BAMStarSchema

  • BAMArchive

    Follow the steps in How to Restore Databases in the Backup BizTalk Server Job to restore these databases.

    Otherwise,
    if BAM is enabled and is also configured with BM.exe, the correct set of BAM databases must be restored together as a set. To ensure that a complete set of archived data is recovered, the BAM Archive database is backed up after the partition is copied into the BAM Archive, but before the partition is deleted from the BAM Primary Import database. This is performed by modifying the data maintenance SSIS package for each activity by inserting a step to back up the BAM Archive database before the last step "End Archiving."

    The restore procedure for the BAM databases is: If the BAM Primary Import database is restored with the last backup date of x, restore the copies of the BAM Archive and BAM Star Schema databases that correspond to the latest date when the data maintenance SSIS package was run before the date of x.

    After the correct set of BAM databases is identified, restore the SQL Server and SQL Server Analysis Services databases using standard procedures as documented in the SQL Server Books Online for restoring SQL Server databases and SQL Server Analysis Services databases.

See Also

Backing Up the BAM Analysis and Tracking Analysis Server Databases