[SQL Server Analysis Services] - "Errors in the metadata manager" when restoring a backup
I had trouble restoring a SQL Server 2005 Analysis Services backup today due to "Errors in the metadata manager" messages:
The ddl2:MemberKeysUnique element at line 243, column 28420 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy.
Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\...
I'm still a relative rookie with SSAS, but I knew enough to suspect that the solution had nothing to do with the backup. My general programming M.O is to try a few things before I start searching for solutions, but with SSAS errors I give a shot at rebuilding the cube (if applicable) and then search.
Sure enough, others had seen this problem and traced it to SQL Server 2005 SP2 not being installed on the backup source or target. I checked and SP2 hadn't been installed on the target database server. Once I installed SP2 on the target server the restore went just fine.