How I corrupted the database with the ‘shared’ checkbox
Last week I got a request from our customer, that images should be maintainable individually for each language version. Until then, images were shared between versions. But the field came from a base template so it was only one field, that needed change.
I assumed, that shared value would simply become the value for all versions, after unchecking the “shared” flag.
“Easier done than said” I told them and I made the change … on production.
Cue the chaos
After some time we realized, that this had some unwanted side effects.
- Items, that had and English and a German version had kept the value, but only for the English version
- Items, that only had a German version had lost the value completely
This meant, that almost all images were gone from the website, because most items inherited their image field from said base template.
Luckily we are able to retrieve previous backups of the database but in the meantime authors have already created new campaigns and content so I wanted to avoid a complete rollback.
1. Restore from English version
This issue was rather easy to fix with one PSE script:
This script will retrieve the English version of the Image field for every item. If the field has a value, it is written to the German version as well.
This fixed my problem at least for all the items, that had an English version.
The next one was more of a challenge.
Restore from database backup
Here I had some hit and miss approaches but the following ended up doing the trick. First my awesome DevOps colleague restored an older state of the Master database, that had not suffered yet from my unthoughtful actions.
With MS SQL Server Management Studio I connected to the database and ran the following query to retrieve all values of the Image field and the ItemId, they belong to:
Since the field was a shared field, the values were stored in the “SharedFields” table.
With these values, I was able to write a PSE script to set the Image value again for all the items.
This script goes through all rows of the ItemIds and Image values, retrieves the German version of the item and then sets the value for the Image field.
And with that, all images were back in their place :)
Afterall the fix was quite straightforward, but still took me almost two days to figure out all the neccessary steps. So maybe these scripts will save someone elses time in the future.
- Even one checkbox can have a severe impact.
- Never make changes directly on PROD (who would have thought).
- Make a backup package before you make any changes.