Q: Why on SSAS 2005 after restoring database backup I am getting corruption error?

Q: Why on SSAS 2005 after restoring database backup I am getting error while querying one of the cubes:

File system error: The following file is corrupted: Physical file: \\?\e:\AS_Data\<DatabaseID>\<CubeID>.cub\<MeasureGroupID>.0.det\<PartitionID>.0.prt\6.fact.data. Logical file.

A: There is a known issue with SSAS 2005 backup - it fails to properly backup disk files that are larger than 2GB. So after you restore such backup, some of the partitions might become corrupted and you are getting above listed message.

Solution to this problem:

• When you see that some of your partitions are approaching 2GB size, consider splitting them into smaller partitions.
• In SSAS 2008 backup/restore was rewritten to use new format, so if you will move to SSAS 2008 this problem will be solved.
• If you are copying database from one server to another, the you could use file level backup/restore. This option will also work faster, but it requires you to copy all databases on one server and it requires you stopping source and destination server for some period of time.

To do file level backup/restore:

1. On the source server stop Microsoft SQL Server Analysis Services service
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.
6. Start destination SSAS service.

Consider also reading SQL Cat paper: "Sample Robocopy Script to customer synchronize Analysis Services databases"

This post was created based on Akshai Mirchandani (Microsoft SSAS Team) explanation.