Have you ever had one of those moments where the User Profile Sync is set to import a property from AD and you desperately need to get the old values back but don’t want to restore the entire User Profile Service?

This SQL script can be used to extract the properties from a restored copy of the User Profile DB without needing to attach it to SharePoint.

SELECT upf.NTName AS Login, upf.PreferredName AS Name, upf.Email, upf.LastUserUpdate AS Modified, pl.PropertyName AS Property, upv.PropertyVal AS Value,upv.SecondaryVal AS SecondaryValue
FROM [Temp-UPSA].dbo.UserProfile_Full AS upf INNER JOIN
[Temp-UPSA].dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
[Temp-UPSA].dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID
WHERE pl.PropertyID = 11

Change the value of the “PropertyID” in the last line to the value from the PropertyList table (PropertyID 11 = Office)

Please note that Microsoft doesn’t support directly accessing or updating SharePoint databases, however in this case we are extracting from a restored database only. The next step is to get the data back into SharePoint using PowerShell.