How to re-provision SharePoint user profile sync without loosing any user data

Here you have to remember on things to disable the “My Site Cleanup Job” before resetting the Sync database, otherwise you run the risk of it deleting all the user profiles in the farm as we found out during a reset: http://technet.microsoft.com/en-us/library/ff681014.aspx#resetSync
User profile sync serves as a staging area for data, and the data in profile store and social databases is the data consumed by the user profile service. So if the right steps are followed, you should be able to reset the sync service and related databases, without harming the user profile data. Here are a set of suggested steps:

1) Login as farm account
2) Backup the User Profile DB and the User Profile Sync DB
3) Stop the SharePoint Timer service:
PS D:\> net stop sptimerv4
4) Delete the data in the Sync DB using the following PowerShell script:
PS D:\> Get-SPDatabase
5) Copy the GUID associated with the User Profile Sync DB in the command line below
PS D:\> $syncdb=Get-SPDatabase -Id
6) Execute these commands, in exactly the following order. This is not a script. So please cut and paste each of these commands one by one.
PS D:\> $syncdb.Unprovision()
PS D:\> $syncdb.Status=’Offline’
PS D:\> Get-SPServiceApplication
#Copy the GUID associated with the User Profile Service and paste it after “Id” in the next command:
PS D:\> $upa=Get-SPServiceApplication -Id $upa.ResetSynchronizationMachine()
PS D:\> $upa.ResetSynchronizationDatabase()
7) Provision the Sync DB:
PS D:\> $syncdb.Provision( )
8 ) Add the User Profile Synchronization service account (farm account) as the dbowner on the Sync DB (using SQL Server Management Studio).
9 ) Start the SharePoint 2010 Timer service
PS D:\> net start sptimerv4
10) Start the User Profile Synchronization Service in the Central Administration UI.
11) After the User Profile Synchronization Service is started, reset IIS.
PS D:\> iisreset
12) Create connections to data sources in the Central Administration UI.
13) Run full user profile synchronization.

About Krishana Kumar

Krishana Kumar is SharePoint Architect/Trainer having Architecture experience with high volumes at Enterprise level and global scale - creation of highly scalable solutions with global user base and geographically distributed architectural components. Good knowledge of SharePoint best practices and governance models. I hold Two Master degree in Computer Science with over 11 years of experience working on Microsoft Technologies specially SharePoint, Project, .NET and other Information Worker Technologies. Having good exposer in Client side scripting Angular.js, backbone and Node. I am currently responsible for SharePoint Infrastructure set up and leading teams in various medium and large scale projects, architecting, designing & installing SharePoint farms, developing custom components,, and providing advanced SharePoint administration and development training to teams and customers. I regularly speaks in various SharePoint User Groups and other Events. I have MCSA Windows Azure, MCSA Office 365, MCSE & MCSD SharePoint 2013, Microsoft Certified Developer (MCD) and holds MCPD, MCTIP and MCTS for SharePoint 2010, MCTS MOSS 2007 & WSS 3.0, MCPD, MCITP (EPM 2010 & 2007) and MCSD .NET.
This entry was posted in General Interest. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *