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\1.fact.data">\\?\G:\AS_Data\<DatabaseID>\<CubeID>.cub\<MeasureGroupID>.1.det\<PartitionID>.1.prt\1.fact.data. 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.
https://msdn.microsoft.com/en-us/library/ms189349(v=sql.90).aspx
Conclusion
Above mentioned are just workaround , permanent solution is moving to SQL 2008 as this issue is fixed in it.