Missing user profile Data

Copy pasted below post for further reference from

http://www.mavention.nl/blog/user-profiles-lost-data

 

User profiles lost data

The Issue

A customer of mine had some issues with their user profiles. It turned out that after some patches and updates (including AD updates and SharePoint SP1 June CU), some user profiles turned out to have lost the profile information. The loss of information consisted of:

  • All colleagues that were followed
  • All SharePoint user profile properties that were not mapped to AD properties

Another issue was that people could not be found when searching for them. You could go to their profile page though.

The Investigation

We stopped the My Site cleanup timer job in order not to lose any more information.

We have restored a backup of the User Profile database and investigated the table UserProfile_Full. What we noticed was that the people that could not be found were in the table, but were flagged for deletion (bDeleted=true). That would explain why they could not be found when searching for them.

Another thing we noticed was that users had gotten a new record ID. Apparently the original records were deleted and new records were created. That would explain why the SharePoint user profile properties and the colleagues they followed seemed to have been cleared.

The Problem

There were now two questions:

  1. How did this happen so we can try to prevent this from happening in the future?
    This is still under investigation. There were some changes to AD causing some outage and the SharePoint Service Pack 1 with June CU might have caused some issues with the AD connection. We don’t have a final answer yet.
  2. How do we get the data back?
    To get the data back we created a tool consisting of two steps:
  • Get data out of the restored database
  • Put this data back into SharePoint

The Solution

Resynchronize user profiles

After we ensured the connections to AD were working again, we ran a full User Profile Synchronization and reviewed the user profiles that initially were flagged for deletion. The flags were reset. So now we could restart the My Site cleanup timer job.

Get data out of the database

We created a SQL query that would get all user profile data from the restored database and store that as an xml document:

/*
 Gets property values for
 – About Me
 – Ask me about
 – Skills
 – Interests
 – Birthday
 – Custom-Role
 – Custom-PreferredFirst
*/
SELECT (
  SELECT
    U.NTName AS [@AccountName],
    (SELECT
      (SELECT
        P.PropertyID AS [@ID],
        P.PropertyName AS [@Name],
        COALESCE(V.SecondaryVal, V.PropertyVal) AS [@Value]
      FROM
        [Restored Profile DB].dbo.UserProfileValue V (NOLOCK) INNER JOIN
        [Restored Profile DB].dbo.PropertyList P (NOLOCK) ON
          (V.PropertyID = P.PropertyID)
      WHERE
        P.PropertyID IN (16, 5005, 5006, 5007, 5010, 10009, 10011) AND
        U.RecordID = V.RecordID
      ORDER BY P.PropertyID
      FOR XML PATH(‘Property’), Type)
    FOR XML PATH(‘Properties’), Type)
  FROM
    [Restored Profile DB].dbo.UserProfile_Full U (NOLOCK)
  ORDER BY U.NTName
  FOR XML PATH(‘UserProfile’), Type)
FOR XML PATH(‘UserProfiles’)

 

The Coalesce function enables us to get either the secondary value (which is used to represent the value for lookup values) if it has been set, or the property value.

The example output is as follows:

<UserProfiles>
  <UserProfile AccountName=”MYDOMAIN\Administrator”>
    <Properties>
      <Property ID=”16″ Name=”AboutMe” Value=”admin on this machine” />
      <Property ID=”5005″ Name=”SPS-Responsibility” Value=”everything” />
      <Property ID=”5006″ Name=”SPS-Skills” Value=”sp” />
      <Property ID=”5006″ Name=”SPS-Skills” Value=”sharepoint” />
      <Property ID=”5006″ Name=”SPS-Skills” Value=”2010″ />
      <Property ID=”5006″ Name=”SPS-Skills” Value=”web” />
      <Property ID=”5006″ Name=”SPS-Skills” Value=”code” />
      <Property ID=”5007″ Name=”SPS-Interests” Value=”sp2010″ />
      <Property ID=”5007″ Name=”SPS-Interests” Value=”sharepoint” />
      <Property ID=”5007″ Name=”SPS-Interests” Value=”2010″ />
      <Property ID=”10009″ Name=”Custom-Role” Value=”sharepoint consultant” />
    </Properties>
  </UserProfile>
  <UserProfile AccountName=”MYDOMAIN\user1″>
    <Properties>
      <Property ID=”5006″ Name=”SPS-Skills” />
      <Property ID=”5010″ Name=”SPS-Birthday” Value=”2000-07-01T00:00:00″ />
    </Properties>
  </UserProfile>
  <UserProfile AccountName=”MYDOMAIN\User2″>
    <Properties />
  </UserProfile>
</UserProfiles>
 

Interesting thing to see is that multi valued profile properties are stored in a record per value.

We created another script that gets all colleagues for a person:

/*
  Gets colleagues for all users
*/
SELECT (
  SELECT
    U.NTName AS [@AccountName],
    (SELECT
      (SELECT
        C.NTName AS [@Name],
        UC.GroupType As [@GroupType],
        UC.GroupTitle AS [@GroupTitle],
        UC.IsWorkgroup AS [@IsWorkGroup],
        UC.ItemSecurity AS [@ItemSecurity]
      FROM
        [Restored Profile DB].dbo.UserColleagues UC (NOLOCK) INNER JOIN
        [Restored Profile DB].dbo.UserProfile_Full C (NOLOCK) ON
          (UC.ColleagueRecordId = C.RecordID)
      WHERE
        U.RecordID = UC.RecordId
      FOR XML PATH(‘Colleague’), Type)
    FOR XML PATH(‘Colleagues’), Type)
  FROM
    [Restored Profile DB].dbo.UserProfile_Full U (NOLOCK)
  ORDER BY U.NTName
  FOR XML PATH(‘UserProfile’), Type)
FOR XML PATH(‘UserProfiles’)
 

That generates the following example output:

<UserProfiles>
  <UserProfile AccountName=”MYDOMAIN\Administrator”>
    <Colleagues>
      <Colleague Name=”MYDOMAIN\User2″ GroupType=”2″ GroupTitle=”” IsWorkGroup=”0″ItemSecurity=”1″ />
      <Colleague Name=”MYDOMAIN\user1″ GroupType=”2″ GroupTitle=”” IsWorkGroup=”0″ItemSecurity=”1″ />
    </Colleagues>
  </UserProfile>
  <UserProfile AccountName=”MYDOMAIN\user1″>
    <Colleagues>
      <Colleague Name=”MYDOMAIN\Administrator” GroupType=”2″ GroupTitle=””IsWorkGroup=”0″ ItemSecurity=”1″ />
      <Colleague Name=”MYDOMAIN\User2″ GroupType=”2″ GroupTitle=”” IsWorkGroup=”0″ItemSecurity=”1″ />
    </Colleagues>
  </UserProfile>
  <UserProfile AccountName=”MYDOMAIN\User2″>
    <Colleagues>
      <Colleague Name=”MYDOMAIN\user1″ GroupType=”2″ GroupTitle=”” IsWorkGroup=”0″ItemSecurity=”1″ />
    </Colleagues>
  </UserProfile>
</UserProfiles>
 

Now this data is for all users. We only need the information from user profiles that have been recreated. For this we created a temporary table:

DECLARE @Users TABLE
(
  NTName nvarchar(400)
)
 
INSERT INTO @Users
  SELECT
    CurrentProfile.NTName
  FROM
    [Restored Profile DB].[dbo].[UserProfileValue] AS RestoredValues
     (NOLOCK) INNER JOIN
    [Restored Profile DB].[dbo].[UserProfile_Full] AS RestoredProfile
     (NOLOCK) ON
      (RestoredValues.RecordID = RestoredProfile.RecordID) INNER JOIN
    [Current Profile DB].dbo.UserProfile_Full AS CurrentProfile
     (NOLOCK) ON
      (RestoredProfile.NTName = CurrentProfile.NTName AND
       RestoredProfile.RecordID <> CurrentProfile.RecordID)
    LEFT OUTER JOIN
    [Current Profile DB].dbo.UserProfileValue As CurrentValues
     (NOLOCK) ON
      (CurrentProfile.RecordID = CurrentValues.RecordID AND
       RestoredValues.PropertyID = CurrentValues.PropertyID)
    INNER JOIN
    [Current Profile DB].dbo.PropertyList AS CurrentProperty
     (NOLOCK) ON
      (RestoredValues.PropertyID = CurrentProperty.PropertyID)
  WHERE
    (CurrentValues.PropertyVal IS NULL) AND
    (RestoredValues.PropertyVal IS NOT NULL) AND
    RestoredValues.PropertyID IN
       (16, 5005, 5006, 5007, 5010, 10009, 10011)
  GROUP BY
    CurrentProfile.NTName
  ORDER BY
    CurrentProfile.NTName ASC
 
 
SELECT *
FROM @Users
 

The idea is that the NTName field will not change, so we took that as the key field to join the user profiles in the current and the restored database. We only wanted those user profiles that had information set in the restored database and not set in the current database.

Another thing to note is that we made a backup of the current User Profile database and restored that one as Current Profile DB. This to ensure we would keep our SharePoint environment in a supported state.

Now we joined the temp table @Users with the two previous queries. That gave use two XML files with all data we needed to restore.

Put the data back into SharePoint

So now we had to get this data back into the User Profiles. The following code snippet processes the profile properties:

public static void RecoverUserProfileProperties(UserProfileManager upm,
        XmlDocument xmlDocument,
        string userProfilesPath,
        string userProfilePath,
        string userAccountNameField) {
    XmlNodeList userProfileNodes = xmlDocument.SelectNodes
         (userProfilePath + “/” + userProfilePath);
    foreach (XmlNode userProfileNode in userProfileNodes) {
        XmlNode attribute = userProfileNode.Attributes[userAccountNameField];
        if (attribute != null) {
            string userAccountName = attribute.Value.ToString();
            UserProfile profile = upm.GetUserProfile(userAccountName);
            if (profile != null) {
                if (RecoverProperties(userProfileNode, profile)) {
                    try {
                        profile.Commit();
                    }
                    catch (Exception ex) {
                        Console.WriteLine
                         (”  Profile not commited. Reason: {0} {1}”,
                        ex.Message,
                        ex.StackTrace);
                    }
                }
            }
        }
    }
}
 
private static bool RecoverProperties(XmlNode userProfileNode,
        UserProfile profile) {
    int currentPropertyId = 0, nrOfProcessedProperties = 0;
 
    foreach (XmlNode propertyNode in userProfileNode.SelectNodes
         (propertiesPath + “/” + propertyPath)) {
        XmlNode idAttribute = propertyNode.Attributes[propertyIdField];
        XmlNode nameAttribute = propertyNode.Attributes[propertyNameField];
        XmlNode valueAtribute = propertyNode.Attributes[propertyValueField];
 
        if (idAttribute != null && nameAttribute != null &&
                valueAtribute != null) {
            int propertyId = Convert.ToInt32(idAttribute.Value.ToString());
            string propertyName = nameAttribute.Value.ToString();
            string propertyValue = valueAtribute.Value.ToString();
            switch (propertyId) {
                case 5005: // Ask me about
                case 5006: // Skills
                case 5007: // Interests
                    if (currentPropertyId != propertyId) {
                        profile[propertyName].Clear();
                    }
                    try {
                        profile[propertyName].Add(propertyValue);
                        nrOfProcessedProperties++;
                    }
                    catch (TermStoreOperationException ex) {
                        Console.WriteLine
                         (“<ERR> Property {0} with value {1} raises error: {2} {3}”,
                        propertyName, propertyValue, ex.Message, ex.StackTrace);
                    }
                    break;
                default:
                    profile[propertyName].Value = propertyValue;
                    nrOfProcessedProperties++;
                    break;
            }
            currentPropertyId = propertyId;
        }
    }
    return nrOfProcessedProperties > 0;
}
 
 
A similar code snippet was created for importing the colleagues:
public static void RecoverUserColleagues(UserProfileManager upm,
        XmlDocument xmlDocument,
        string userProfilesPath,
        string userProfilePath,
        string userAccountNameField)
{
    XmlNodeList userProfileNodes = xmlDocument.SelectNodes
         (userProfilePath + “/” + userProfilePath);
    foreach (XmlNode userProfileNode in userProfileNodes) {
        XmlNode attribute = userProfileNode.Attributes[userAccountNameField];
        if (attribute != null) {
            string userAccountName = attribute.Value.ToString();
            UserProfile profile = upm.GetUserProfile(userAccountName);
            if (profile != null) {
                RecoverColleagues(upm, userProfileNode, profile)
            }
        }
    }
}
 
private static void RecoverColleagues(UserProfileManager upm,
        XmlNode userProfileNode,
        UserProfile profile) {
    ColleagueManager colleagueManager = profile.Colleagues;
 
    foreach (XmlNode colleagueNode in
        userProfileNode.SelectNodes(colleaguesPath + “/” + colleaguePath)) {
        XmlNode nameAttribute =
                colleagueNode.Attributes[colleagueNameField];
        XmlNode groupTypeAttribute =
                colleagueNode.Attributes[colleagueGroupTypeField];
        XmlNode groupTitleAttribute =
                colleagueNode.Attributes[colleagueGroupTitleField];
        XmlNode isWorkGroupAttribute =
                colleagueNode.Attributes[colleagueIsWorkGroupField];
        XmlNode itemSecurityAtribute =
                colleagueNode.Attributes[colleagueItemSecurityField];
 
        if (nameAttribute != null && groupTypeAttribute != null &&
                groupTitleAttribute != null && isWorkGroupAttribute != null &&
                itemSecurityAtribute != null) {
            string colleagueName = nameAttribute.Value.ToString();
            string colleagueGroupType = groupTypeAttribute.Value.ToString();
            string colleagueGroupTitle = groupTitleAttribute.Value.ToString();
            string colleagueIsWorkGroup = isWorkGroupAttribute.Value.ToString();
            string colleagueItemSecurity = itemSecurityAtribute.Value.ToString();
 
            if (upm.UserExists(colleagueName)) {
                UserProfile colleagueProfile =
                        upm.GetUserProfile(colleagueName);
                if (colleagueProfile != null) {
                    if (!colleagueManager.IsColleague
                         (colleagueProfile.RecordId)) {
                        ColleagueGroupType groupType =
                           (ColleagueGroupType)Convert.ToInt32(colleagueGroupType);
                        bool isInWorkGroup = Convert.ToInt32
                           (colleagueIsWorkGroup) == 0 ? false : true;
                        Privacy privacyLevel = (Privacy)Convert.ToInt32
                           (colleagueItemSecurity);
 
                        try {
                            colleagueManager.CreateWithoutEmailNotification
                               (colleagueProfile,
                                groupType,
                                colleagueGroupTitle,
                                isInWorkGroup,
                                privacyLevel);
                        }
                        catch (Exception ex) {
                            Console.WriteLine
                                 (”  Colleague not added. Reason: {0} {1}”,
                                ex.Message, ex.StackTrace);
                        }
                    }
                }
            }
        }
    }
}
 

For simplicity sake the code has been condensed.

Wrap up

If you lose user profile data, you can get your data back by querying against a restored database and saving the result into an XML file. This XML file can then be used by a application that will import the data by using the SharePoint object model.

In fact this procedure would also work for any other SharePoint entity. You could even use this as a basis to import data from other sources. Another step I already implemented is the other way around: get data from SharePoint User Profiles and store it into an XML document.

Note: One minor drawback of using SQL queries against the SharePoint databases is that you have to be aware of possible future schema changes.

Advertisements