[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.