How to import Jira Agile Sprint data project by project

[Lee Webb]

Hello world,

I wanted to share some frustration regarding the general lack of support for exporting & importing Active Objects based data which a lot of JIRA plugins tend to use.

For those of you who are voting madly on https://jira.atlassian.com/browse/JRA-28748 this might help you if you're faced with:

• Migrating from an OD JIRA Instance to a Standalone version
• Your Standalone version already exists & you need to merge the OD data with your existing Standalone project by project
• You only care about saving the "Sprint" fields & the Boards which use them

Where possible I really encourage you to "Restore System" from the OD XML dump rather than going project by project, so if your organisation is moving away from OD then really consider moving the while OD XML backup to Standalone & then manipulate from there - going project by project is hard, but not impossible.

In my case I had to preserve the "Sprint" custom field values in particular - I didn't care about rank or some of the other information - this is where the "Some" comes from in the summary.

You'll probably already know that you can't just import the value of the Custom Field for the JIRA Agile fields - they import but don't link up with the Boards.

As with most project by project imports I'm assuming you've already taken care of workflows, custom fields, screens, permissions, priorities, roles etc. etc. etc. then created an empty project that you want as a container for the resulting import.

1. Make a backup of the OD instance
2. Take this backup & system restore it into a Standalone instance matching your final Production instance version
3. Make sure that you install & get trial licenses for all of the OD plugins you had installed
4. Reconnect the Marketplace in the Addons area
5. Conduct an XML backup on this Standalone instance

Now the fun begins

1. On your production system duplicate the Boards you created in OD, this should also include any filters which were made in OD too

2. Using the database of your Standalone instance (the one you imported OD into) export out the AO_xxxxx_SPRINT & AO_xxxxx_AUDITENTRY tables, this should be done ideally to ASCII as INSERT statements. These contain the information associated with the "Sprint" custom field
   The names of the tables can be taken from the Plugin Data Storage list in the System admin area 

   sudo -u postgres pg_dump -a --inserts -t \"AO_60DB71_SPRINT\" -t \"AO_60DB71_AUDITENTRY\" jira > AO_60DB71.sql

3. Grab a mapping of the RAPID_VIEW_ID's for all your boards in OD vs. your Production Instance (you can grab this by navigating to the board & grabbing it from the URL)

4. Change the entries in the AO_xxxxx_SPRINT table so that the RAPID_VIEW_ID's align with your Production Instance, something like

   UPDATE "AO_60DB71_SPRINT" set "RAPID_VIEW_ID" = 6 WHERE "RAPID_VIEW_ID" = 3;
   UPDATE "AO_60DB71_SPRINT" set "RAPID_VIEW_ID" = 5 WHERE "RAPID_VIEW_ID" = 12;
   UPDATE "AO_60DB71_SPRINT" set "RAPID_VIEW_ID" = 4 WHERE "RAPID_VIEW_ID" = 13;

5. Insert the munged AO_xxxxx_SPRINT & AO_xxxxx_AUDITENTRY tables with the updated mappings into your target system
6. Stop the target system JIRA instance & then start it again, once running reindex
7. You should then see your Sprints in the empty boards & they'll have the backend ID's which the original OD version did (important because we're going to remap them all)

8. Back on the Standalone system we now need to grab all the customfieldvalues for all the Sprint fields for the old issues, we're going to use this to edit all the post imported issues later to populate out boards
   
   You need to know 2 things: the custom field id of the source system & the destination for the Sprint field
   
   In my systems this was 10007 for the source & 10306 for the destination (you can get this from the Custom fields page in the Admin area of JIRA by clicking on edit or view, grabbing it from the URL) 
   
   Then run this, changing the field id's to suit: 

   IFS=$'\n';
   for l in `sudo -u postgres psql -t -c "select p.pkey || '-' || i.issuenum,c.stringvalue from customfieldvalue c join jiraissue i on c.issue = i.id join project p on i.project = p.id where c.customfield = 10007" jira | sort -k 1`; do
   id=$(echo ${l} | awk -F\| '{print $1}' | sed "s/ //g");
   sprint=$(echo ${l} | awk -F\| '{print $2}' | sed "s/ //g");
   echo curl -D- -u \$\{u\}:\$\{p\} -X PUT -H \'Content-Type: application/json\' -d \'{ \"fields\" : { \"customfield_10306\": \"${sprint}\" } }\' \$\{h\}/rest/api/2/issue/${id}
   done

   The above will output a bunch of Curl commands to hit the REST API and set the desired Sprint ID on the issue, where:
      u = the username to auth as
      p = the password for the username
      h = the hostname to hit where the REST API is
   Keep this shell script for later on

9. Now we're ready to do the project import - do this normally via the JIRA Admin interface, you should have no errors (watch out for https://jira.atlassian.com/browse/JRA-41681 too)

10. Ensure that the workflow bound to the recently imported project allows for editing of all issues (including closed ones), so that we can sort the history out
11. Now run the shell script which will modify all the issues & set the proper Sprint CF value
12. At this point your Boards should start populating based on the Sprint values being set, you'll also notice that the reports will show past Sprints & the issues associated with them

There's obviously no warranty on this & Atlassian most certainly won't help you with this (other than to point you at an "expert")

It works though, good luck if you want to give it a go

[Pierre Sauveur]

Hello

NB: my experience is for JIRA 6.2.7, with JIRA Agile 6.6.80

I followed a similar way but still the result was not satisfying. The Sprint report on closed sprints showed big mistakes:

• not any request I associated with a given sprint was shown in the "Completed Issues" list. Instead, they were all in the "Removed From Sprint" section (NB: in the source instance, most of them were in the "Completed Issues" or "Issues Not Completed" sections)

• moreover, I found issues associated to closed sprints that were absolutely not related to those spprint (and which I never associated with thos sprint myself, obviously)

Apparently, this is caused by the new entries created in the issues history when the sprint association is rebuilt.

I found a way to make things better, and though the matching before/after is not 100% accurate (ie: issues not in the good section of the Sprint report, missing "Added after the sprint start" note), it still was much better than leaving it as it was.
Here are the steps I would add to Lee's instructions:

13. delete all change history related to the Sprint reaffectation you made in step 11 with those DB requests (replace author and date at your convenience):

delete changeitem
where exists (select * from changegroup g where g.CREATED between '2016-04-05 19:00:00' and '2016-04-06 08:00:00' and author ='admin' and g.id=groupid)
and field ='Sprint';
delete changegroup where CREATED between '2016-04-05 19:00:00' and '2016-04-06 08:00:00' and author ='admin'
and not exists (select * from changeitem i where i.groupid=changegroup.ID);

14. restart Jira and reindex (actually reindexing seems enough, but I think Atlassian recommends to restart after a database modification)

Thank you for your inital post, Lee

Edit : for the record, I also encountered other issues, even with the 2 additional steps, still related to JIRA Issues History.

As I was merging 2 instances, some of the IDs were not the same from source to target instances. Not a big thing, except for the Issue history: while importing the project in the new instance, the IDs stored in the issues history (columns "oldvalue" and "newvalue" of table "changeitem" in the database) are kept as they were in the source instance. This is problematic to JIRA Agile with (at least) the following fields:

• Status. ie: you recreated a status existing in the old instance, and the id is obviously not the same
• Sprint. ie: unlike Lee, you recreated manually the sprints (cf answer to Bruce below, concerning SQL server)

This caused isssues to be affected to wrong Sprints, or being considered as finished in the sprint they were finished.

In that case, you will need to update the table CHANGEITEM, and modify the values in columns OLDVALUE and NEWVALUE were they became inconsistent with the real IDs.

Since the solution is really dependent of the configuration, I can't provide a generic script. For myself, I mostly extracted the data in excel, analysed what needed to be changed, and wrote my SQL request.

Don't forget to restart JIRA and reindex once changes are done.

[Daniel Ciaglia]

Hi Areg, did you find the time to extend the above instructions with REST calls?
We are facing the same question now.

Regards,
Daniel 

[Bruce Taggart]

Anybody been able to do this on a Windows platform with other databases (for example, mysql or sql server)?

[Pierre Sauveur]

Hello Bruce

In case this is still usefull to you, I just made it on a windows platform, with SQL Server (2008 I think).

I did not follow Lee's instructions exactly, as I found this thread only after I finished my migration (cf my answer above), but followed a similar path. The main difference I can tell is that the step concerning the replication of tables AO_60DB71_SPRINT and AO_60DB71_AUDITENTRY will not work on a SQL Server DB as they are proposed by Lee.

In fact, on SQL Server, you won't be able to set the ID in this table, they are configured to set it automatically, and prevent you from forcing it.

What I did was to recreate the sprints from Jira, and then set the Sprint values of the imported issues with the new IDs of the sprint.

From my point of view, Lee's solution is much easier since you do not need to mess with Change History of issues (cf last part of my post), but:

• it is more difficult to use with a SQL Server DB
• it may not even be possible to use if you are merging two instances of Jira, having each one their own sprints

Except that, everything should work the same I think

[Areg Vrtanesyan _Work_]

Hi Lee Looks like for the latest Jira and Agile it is better to use the Agile REST API rather then Issues API - https://docs.atlassian.com/greenhopper/REST/cloud/#agile/1.0/sprint-moveIssuesToSprint {noformat} curl -D- -u admin:admin -X POST -H 'Content-Type: application/json' -d '{"issues":["ISSUE-1","ISSUE-2"]}' http://jira:8080/rest/agile/1.0/sprint/<Sprint ID>/issue {noformat} In this case it is possible to avoid errors during REST execution when workflow has an option that Issues are not editable in closed state. Also for big Jira instances it is necessary to take in account that Sprints will have new ID's in the destination instance and it is necessary to translate ID's for Sprints too. (The ID column in Database for the Sprints Table is autonumbered for me on MySQL databse.) I am putting together the steps how to add Agile data to the destination Jira Instance and looks like I can make improvements to your documentation by adding some REST calls to the source Jira to gather data and inject it to the Destination avoiding SQL manipulations ... Do you have an idea if the historical data for the old sprints could be transferred too - like the data for graph of sprint activity shown in the Sprints Reports? Looks like it is getting lost during the transfer and I am not quite sure if it will be possible to recover at all. In any case your solution quite good and BIG THANKS for that. Regards, Areg

[April]

Lee, you're a real gentleman!