SSAS 2005 Backup/Restore Limitation


In SQL 2005, you may get below mentioned error after restoring SSAS Database using .abf file

File system error (sample): The following file is corrupted: Physical file: \.cub\.1.det\.1.prt\">\\?\G:\AS_Data\<DatabaseID>\<CubeID>.cub\<MeasureGroupID>.1.det\<PartitionID>.1.prt\ Logical file.

It’s a known issue with SQL 2005 backup, which is fixed in SQL 2008. SQL 2005 backup fails to properly backup files which are larger than 2 GB. So if you restore the backup it restores corrupted version of files and end up with error message.

This issue is permanently fixed in SQL 2008 and above build of Analysis Services

Interim workaround incase you cant move to SQL 2008

Instead of taking backup using inbuilt backup feature of SSAS 2005, use Disk level backup.

Ensure that you will Stop Analysis Services before taking SSAS Data Disk Level backup, otherwise you may face corruption.

Few other scenarios of making copies of your Production SSAS Database apart from Disk Level backup

Scenario 1

Copy Data Directory From Source to Backup Server / Destination.

If you are bringing both server offline:

1.    Stop AS Services on Source and Destination Server (Ensure that no user / processing is going on – check profiler trace for existing users)

2.    Copy analysis services data folder to destination location (if there is existing data directory on destination server rename it to old)

3.    Start source SSAS service (Once copy is done completely)

4.    Copy data folder from step 2 to new SSAS data folder location

5.    Start destination SSAS service.

If you are keeping destination server online:

1. On the source server stop Microsoft SQL Server Analysis Services service  (Ensure that no user is connect to server)

2. Copy analysis services data folder to new location

3. Start source SSAS service

4. Stop destination SSAS service

5. Copy data folder from step 2 to new SSAS data folder location or update the msmdsrv.ini file on destination server pointing to new data directory – steps below mentioned.

6. Start destination SSAS service.

How to update msmdsrv.ini file

Ensure that data directory is located on new location.

Old Location is C:\Data

New Location is d:\Data

Open Msmdsrv.ini file (?:\Program Files\Microsoft SQL Server\MSAS\OLAP\Config)

<DataDir>c:\data</DataDir> old value

<DataDir>d:\data</DataDir> old value

Scenario 2

(Source server should be online for taking XMLA Script)

Open Management on Source Server, right click on database which is in context.

Click on Script Database As -> Create to -> Script to a file

Take that file to Destination Server, open that file in management Studio and execute this file

Once database is created successfully, right click on database, rename it and do full processing.

Before executing Script - You can rename the database name by changing the database id and name.

Scenario 3

Synchronize Database from source to destination server.


Above mentioned are just workaround ,  permanent solution is moving to SQL 2008 as this issue is fixed in it.

Comments (0)

Skip to main content