How To Restore Resolution Date after Bulk Update with Script Runner

Question

Hi,

I recently used the Script Runner to bulk edit the Resolutions on a bunch of my issues.  When I ran the script, I was unaware that it also changed the Resolution Date of the issues, so now all of my Resolution Dates are messed up.  Is there any way to restore the previous Resolution Date using Script Runner?

NOTE: I have already explored the ChangeHistoryManager in the Script Runner console.  Unfortunately, it looks like updates to the Resolution made via the Script Runner bulk update do not appear in the change history.  Changes to the resolution that take place due to workflow activities do show up in the change history.

Thanks in advance for the help!

Ameya · Answer

This issue happened with me in a critical project. The solution is for Jira DC 7.13 and PostgreSQL. Please try the same on Lower environment before doing it Live. It works.

The steps are:

* Spin up a copy of the Jira database backup from the latest version before the issue occured
* Export the id and resolutiondate values to a CSV file:\copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/output-dev.csv' with csv;

* Manually add a header of id and resolutiondate to the CSV.
* Back up the potentially affected issues in the jiraissue table:\copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/prod-backup.csv' with csv;

* Create a temporary table in Jira DB to house the updated value:CREATE TABLE tmp_issues (id numeric(18,0), resolutiondate timestamptz);

* Import issues from the CSV generated in step 2 to the table we just created:\copy tmp_issues(id, resolutiondate) FROM '/path/to/output-dev.csv' DELIMITER ',' CSV HEADER;

* Confirm this data looks correct:SELECT * FROM tmp_issues limit 50;
<two columns with id and resolutiondate>
\d tmp_issues
               Table "public.tmp_issues"
     Column     |           Type           | Modifiers 
----------------+--------------------------+----------- id             | numeric(18,0)            | 
 resolutiondate | timestamp with time zone |

* Update the issues using our temp table:UPDATE jiraissue SET resolutiondate = tmp_issues.resolutiondate FROM tmp_issues WHERE jiraissue.id = tmp_issues.id;

* Once this finishes, perform a rolling restart of Jira.
* Drop temporary table:DROP TABLE tmp_issues;

* Clean up jiraissue table:VACUUM jiraissue;

* Perform a project reindex of Affected project.

Ameya · Answer

The Fix is for Jira DC. We had issues that were affected in a critical project. Please test the same on lower environment before doing it Live.

The Steps:

* Spin up a copy of the Jira database backup from the latest version 
* Export the id and resolutiondate values to a CSV file:\copy id, resolutiondate FROM jiraissue WHERE project = "ID" AND resolutiondate IS NOT NULL) to '/tmp/output-dev.csv' with csv;

* Manually add a header of id and resolutiondate to the CSV.
* Back up the potentially affected issues in the jiraissue table:\copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/prod-backup.csv' with csv;

* Create a temporary table in Jira to house the updated value:CREATE TABLE tmp_issues (id numeric(18,0), resolutiondate timestamptz);

* Import issues from the CSV generated in step 2 to the table we just created:\copy tmp_issues(id, resolutiondate) FROM '/path/to/output-dev.csv' DELIMITER ',' CSV HEADER;

* Confirm this data looks correct:SELECT * FROM tmp_issues limit 50;
<two columns with id and resolutiondate>
\d tmp_issues
               Table "public.tmp_issues"
     Column     |           Type           | Modifiers 
----------------+--------------------------+----------- id             | numeric(18,0)            | 
 resolutiondate | timestamp with time zone |

* Update the issues using our temp table:UPDATE jiraissue SET resolutiondate = tmp_issues.resolutiondate FROM tmp_issues WHERE jiraissue.id = tmp_issues.id;

* Once this finishes, perform a rolling restart of Jira.
* Drop temporary table:DROP TABLE tmp_issues;

* Clean up jiraissue table:VACUUM jiraissue;

* Reindex the AFFECTED PROJECT

cjdemattio · Answer

Thank you for the suggestion Alexey.  We do have backups, however, since I didn't discover the problem for a few days, I can't do a wholesale restore for fear of losing information posted since I made my changes.

Fortuntely, we do have a staging server who's last refresh took place just _before_ I made my updates to the resolutions.  So I do have a way to at least recover what the values used to be.  As a last resort, I may have to do what you suggested and iterate over that spreadsheet to push back to the old resolutions dates.

Before I do that, I'm really hoping that somebody from Adaptavist is going to chime in and tell me how to find the old resolution date in some changelog buried deep in JIRA.