After performing a full farm restore from one farm to another, I noticed that the following event is being logged into application event logs in every one hour.
Event ID: 5553
failure trying to synch site <GUID> for ContentDB <GUID> WebApp <GUID>. Exception message was Cannot insert duplicate key row in object ‘dbo.UserMemberships’ with unique index ‘CX_UserMemberships_RecordId_MemberGroupId_SID’.
The statement has been terminated..
After researching for some time I got really confused because all the online discussions talk about content database being detached and attached to a different farm without running “preparetomove” command. But what I did was a farm restore not a content database attach. The solution discussed everywhere was to run the following commands.
stsadm –o preparetomove –contentdb <GUID> –site <URL>
stsadm –o sync –deleteolddatabases 5
But reading further I realized that with SharePoint 2010, there is no longer a “preparetomove” switch with stsadm. SharePoint keeps the same GUID for any content databases attached. Then I found this post and as per the instructions in it, I ran the following command.
stsadm –o sync –deleteolddatabases 0
I waited for few hours for the User Profile to SharePoint Full Synchronization job to complete and verified that the error mentioned above has gone.