How to create a Jira issue hierarchy via sql script

Question

HI everyone,

I have access to DB at the backend of our JIRA Cloud instance. I am trying to build reporting and i want to create a JIRA hierarchy that will show me the Initiative - All the EPIC's linked to it, all the stories, task and subtask linked to that was well and so on. Has anyone done it and can share the concept / code with me?

Thank you

Nic Brough -Adaptavist- · Answer

A backup is not good enough, if you're going to mess with an Atlassian database, you need to do all the other things I've mentioned.

You also need to understand fully how the code understands the database, otherwise you are going to corrupt your database.

The "optimal script" for creating a hierarchy is to do it in the UI.

Korsten Bezuidenhout · Answer

HI @Nic Brough -Adaptavist-

A full back up is done of the instance to our data warehouse and to that i have access.

I have scripted multiple reports from the data warehouse but i am looking for an optimal script to recreate the hierarchy. I cant use the REST API at this point.

Thanks.

Nic Brough -Adaptavist- · Answer

Welcome to the Atlassian Community!

You do not have access to the database on a Jira Cloud instance.

I suspect what you mean is that your organisation is running Jira Server or DC on Cloud "hardware" (AWS, Google compute, Azure, etc) and you have access to the database node(s)

On that, there's a very simple rule - you can never write to an active Jira database.  Most of the code people might give you will be all about how you script to shutdown your Jira, take a full backup of the database, run a bit of SQL, restart Jira and then trigger a full locking re-index on every node in the cluster.

I strongly recommend that you look at doing this hierarchy work with the REST API or scripts and automations, whether it's Atlassian Cloud, or Server/DC run on Cloud provided servers.