Summary

One of our staff members using Rock accidentally merged one of our diligent volunteers, which seemed like a mess to untangle. I worked to create a script that is designed to help recover from an accidental merge in Rock in a way that automatically detects impacted tables with aliases.

Credit Where Credit Is Due...

I found a great Recipe from Greg Lawless of Dwell Community Church on Recovering a Merged Person. Greg's script got me going in the right direction, but the entities his church uses don't quite match the same ones that my church uses. And Greg's recipe mentions that his script is not exhaustive for the entities that could be impacted. So, I wrote a script to detect which entities were in use for the user I was unmerging, and I thought "why not integrate that into the unmerge script?" The results below should allow you to unmerge a person in a way that checks for all tables having columns with PersonAliasId in the column name, allowing you to dynamically update those tables without extensive querying to see which entities are impacted. In other words, this is an attempt to be exhaustive in selecting which entities should be unmerged.

This script also seeks to automate the lookup of Ids for the previous record that was merged and new records with that same name using only the first and last names of the impacted individual. And it should move any user logins that were associated with the accidentally merged record back to that record.

The instructions are in the script, so you can skip to the bottom and follow those from a query editor window in SSMS if you prefer. But I'll detail them here, as well.

Instructions

1. Restore a database backup from as near the time preceding the accidental merge as you have available to the same server where your live Rock database exists. If you restore that backup with the name rockrms-backup, you will save having to replace that name in the script in a handful of locations.
2. Create a new person record for the individual who was accidentally merged in your live Rock instance. If the person was the only one in his/her "family" in Rock, create that record in your current Rock instance in a new family. If the person was a part of a family with others, create the record in your current Rock instance in the original family.
3. Download and open the Unmerge Person with Alias Detection.sql file in a SQL Server Management Studio window connected to your live Rock database instance.
4. If your database names are something other than rockrms for the live database and rockrms-backup for the backup you've restored, you'll need to replace those names with your db name throughout the script (CTRL+H will bring up a Replace window with a Replace All button).
5. Change the @PersonFirstName and @PersonLastName values for the person you're restoring (be sure they're the same in your backup and current Rock database!) NOTE: This will automatically look up values for the @previousPersonId, @currentPersonId, and @currentPersonAliasId variables. If you would rather manually set those, you can uncomment the = statement to the right of those variable declarations and input your own values. You'll also need to comment out or delete the 3 lines of code that look those values up.
6. Run the script first with the ROLLBACK option at the end of the script uncommented (which it should be if you haven't changed it) to ensure the results match your expectations. This will produce results for you to preview.
   • In the Results tab, you should see a couple of tables. The first will display the found Backup and Current records to give you a preview of which previous person record is being unmerged to which current person record. The second table will give you a list of tables where aliases of the individual in the backup database were found in the current/life database, and how many records in that table have one of those previous person aliases. Make sure that the Backup and Current records identify the individuals you expect. And be sure that nothing looks out of the line in the impacted tables.
   • In the Messages tab, you will see a preview of how many records are impacted by the updates. Some of these are just showing that the records have been found and added to the list of tables under consideration, but others near the bottom give a preview of how many rows are actually being impacted when the aliases for the unmerged records are updated. These should generally match the results from the second table in the Results tab. You will want to make sure that the affected row counts at least make sense before committing the updates. Also look to make sure there are no errors signified by red text at the bottom of these messages.
7. Comment out the ROLLBACK TRAN command and uncomment the COMMIT TRAN code at the bottom of the script. After making this change, when you run the script, your results will be committed to your live Rock instance.

Once you've followed these instructions, you should see the records from before the merge associated with your re-added user once again. Verify that the entity data and person attributes have re-appeared on the re-added record. If all looks well, you're done!