I imagine we have all been there, right after you (or another staff member) clicked that merge button you got that sinking feeling in your gut that you just made a mistake.

Merging a person record by mistake can lead to hours of work re-creating a person by hand, and then cleaning up the bits of person info that doesn't belong in the merged record. After confronting this problem and examining the Database it became apparent that most of the person data was still in the Database and had simply been reassigned to the, for lack of a better term, "winner" in the person merge. It looks like the only record deleted from the database was the person record of the "lost" person in the merge ( and possibly the family group if the "lost" record was single, but I am not sure about that). So, with a little SQL and a recent database backup you can automate most of the "Un-merging" process.

In this recipe I will lay out the steps to restoring a merged person mostly automatically using SQL and a Backup.

Entities Included

This is probably not an exhaustive script in terms of entities which it restores. In fact, it only contains the entities which my particular church uses. Once you have observed the pattern of the entity update queries adding new ones to the script should be pretty straight forward. At Any rate, here is a list of Entities which the script restores:

  • GroupMember
  • Attibutes (Person)
  • FinancialPledge
  • FinancialTransaction
  • FinancialScheduledTransaction
  • Attendance
  • CommunicationRecipient
  • Assessment
  • ConnectionRequest
  • Registration
  • RegistrationRegistrant
  • Step


Step 1 - Create A New Person Record

In your live Rock instance create a new family/person record. If the merged person was married, add a new person to the existing family of which they were a part. If they are single create a new family. At this point you will want to collect the new PersonId and PersonAliasId.

To make this easier, and in general for administrators, I put an html block on the person page at the bottom of the Individual Detail section and give only Rock administrators permission to see it.

personPage1.png

You can do this easily with the following html + lava in an html block on the person profile page:

<p style="text-align: center; color: black; ">Person Alais Id: <strong> {{ Context.Person.PrimaryAlias.Id }}</strong><br>
Person Id: <strong>{{ Context.Person.PrimaryAlias.PersonId }} </strong> </p>

Note down both Ids for later use.

Step 2 - Restore A Database Backup

Restore a backup copy of your rock database on to your production server under another name. This backup must be on the same server with your production rock database for this to work. If you are using an Azure DB, then I think you will have some extra steps wiring up the backup and live db (I don't use an Azure DB, so I am not sure how difficult it is to set this up).

The backup copy should be as close as you can get to the merge day. 

Step 3 - Find the Person/PersonAliasId of the Lost Record

In your backup DB, use the following query to find the PersonId and PersonAliasId of the merged person using their FirstName and LastName:

Select 
p.Id PersonId
,pa.Id PersonAliasId
from Person p join
PersonAlias pa on p.Id = pa.PersonId
where FirstName = 'Theodore' 
and LastName = 'Decker'

You may want to add additional details to this query like family group membership (GroupMember.GroupId) or email/birthdate/phone information in cases where the name is not unique (e.g. John Smith). Note down the PersonId and PersonAliasId as you will need them in the next step.

Step 4 - Change the Variables in the SQL Script

Download and open the attached SQL script in SQL Server Management Studio and select your live rock instance from the query toolbar drop down list. You will want to make some changes in this script to fit your situation. It is very important that you to change the following information in this script to match your particular infomation:

  • Anywhere you see 'rocktest.dbo.[table]' in the script you need to change 'rocktest' to the name of your restored database backup
  • Set the @oldPersonId variable to the original PersonId (collected in step 3) for the merged person 
  • Set the @prevaliasid variable to the original PersonAliasId (collected  in step 3) for the merged person
  • Set the @newpersonid variable to the newly created PersonId  (collected in step 1) for the merged person
  • Set the @updatedAliasid variable to the newly created PersonAliasId (collected in step 1) for the merged person
--Orignal Ids
declare @oldPersonId int = [originalPersonId]
declare @prevaliasid int = [originalPersonAliasId]
--New Ids
declare @newpersonid int = [newPersonId]
declare @updatedAliasid int = [newPersonAliasId]

--Find Group Member Ids in the backup database and update the corresponding group member records in the live DB with the new PersonId
update gm
set PersonId = @newpersonid
from GroupMember gm
JOIN (
  select Id
  from [yourRestoredDBName].dbo.GroupMember ogm
  where PersonId = @oldPersonId
  ) ogmid on gm.Id = ogmid.Id
JOIN [Group] g on gm.GroupId = g.Id
where g.GroupTypeId <> 10


Step 5 - Run the Script With "ROLLBACK"

It is good practice to run any SQL script that will be making changes in a database with BEGIN TRAN at the start of the script and ROLLBACK TRAN at the end so you can review the modifications made without making any changes to the data. The attached script has ROLLBACK TRAN at the end by default. After running it you will see the results in terms of rows affected, looking something like this:

(5 rows affected)

(5 rows affected)

(0 rows affected)

(0 rows affected)

(0 rows affected)

(0 rows affected)

(55 rows affected)

(0 rows affected)

(0 rows affected)

(0 rows affected)

(0 rows affected)

(0 rows affected)

Completion time: 2020-07-08T22:26:16.6150621-04:00

You will want to make sure that the affected row counts at least make sense before committing the updates. The order of the list of updated rows will match the order of entity update queries in the script. One way to verify that the number of affected rows is correct is to write a quick select query using the person or personaliasid (which ever is required by the entity) of the "winner" of the merge and look for records from a specific entity with a ModifIedDateTime range that matches with the date and time of the merge. The example below is for the GroupMember entity.

select COUNT(gm.Id)
from GroupMember
where PersonId = [winnerPersonId] 
and ModifiedDateTime between [datetime] and [datetime]

Step 6 - Run the Script in Commit Mode

Once you are comfortable that the changes the script is making to the live database are correct, you can alter the last line from ROLLBACK TRAN to COMMIT TRAN. At this point you are committing the changes made by the script to your live rock instance. Once you have run the script go back to the person page in rock with your newly un-merged person record and look through all of the sub pages and verify that the entity data and person attributes have been assigned to the new record.