How to restore Insight from DB backup without affecting Jira

[Oren]

We want to restore Insight from DB backup of a few days ago, but to keep all other Jira related data.

How can we do this?

[we are using Insight with Jira software on local server installation]

[Oto Machaladze]

Hey, Oren

You can schedule backup for object schemas using a cron expression or a simple scheduler by utilizing the add-on Insight Assets Backup & Migration. Following a backup, you may either make a new Assets object schema or restore the one that was previously there. You can also restore the backup using a different cloud instance.

Please refer to this article for more information:

https://community.atlassian.com/t5/Jira-Service-Management/How-to-backup-and-restore-deleted-or-existing-assets-schemas/m-p/2385035#M3785

Contact us at our service desk if you have any inquiries or suggestions, or leave a comment on the article itself. We can also arrange a demo of the app.

BR,

Oto

[Oren]

If anyone encounter a similar case, you may try to do the Insight's "Clean re-index".
The relevant page is at Jira's administration, on "Manage apps" section, under Insight at left side-bar, click: "Indexing Insight".

In case new objects ware created with same labels as old objects that "disappeared", to avoid conflicts, make sure to delete them before the re-index.

[Nic Brough -Adaptavist-]

There is no way to do this, the Insight data is part of the Jira data and lives in the same database.

Technically, that's not strictly correct.  You could spend months working out each line of data in all the database tables you should remove, insert or update by comparing the current database with your backup, but I fear "months" is probably an understatement of the effort, and, of course, no-one will be able to use Jira (or Insight) while you do that.  So while it is technically possible, it's not a useful solution.

Why are you thinking you need to restore from a backup here?  What have you done that breaks all your Insight data?

[Oren]

Insight data is part of the Jira data and lives in the same database

Yes, but on different tables, and if I'm not mistaken, with no constraints outside of Insight dedicated tables

https://insight-javadoc.riada.io/insight-javadoc-8.4/insight-db-documentation/constraints.html

Why are you thinking you need to restore from a backup here?

We are not sure of the cause, but after a Jira reboot and re-index done on 2021-09-30, many Insight object were gone (more than 1000 out of about 7000).

[Nic Brough -Adaptavist-]

That doc is just about the constraints, it does not tell you where Insight data is in the Jira data, nor how the data in the Insight tables is linked with Jira data.

It's not a totally separate set of tables, it can't be (easily) restored independently.

A restart and re-index will NOT cause database changes either, so a restore probably isn't going to do anything anyway.

You'll need to look at why and how data was deleted from your system before you can be sure that a simple restore from backup would be of any use

[Oren]

That doc is just about the constraints, it does not tell you where Insight data is in the Jira data, nor how the data in the Insight tables is linked with Jira data.

That doc isn't just constraints - it allegedly has all Insight related tables (54) and the relationships between them.

https://insight-javadoc.riada.io/insight-javadoc-8.4/insight-db-documentation/index.html

[above docs are Atlassian doc Referral: https://confluence.atlassian.com/insightapps/insight-database-1085180940.html ]

Do you have experience with Insight internals?

A restart and re-index will NOT cause database changes either, so a restore probably isn't going to do anything anyway.

Restoring lost data is what we need.
We know restart and re-index shouldn't do anything like this, and probably the cause is something else (most of the times it is some human error), but currently this is the only major event in the relevant time-frame.
Nothing related was found in the audit log. We have to find the cause, so we can avoid it in the future, but data mast be restored ASAP.

[Nic Brough -Adaptavist-]

Yes, I have worked with the internals of Insight in Jira, mostly for doing merges and migrations.  Not all of its data is held in those tables, there is data held in the Jira tables.

A simple restore of the insight tables will NOT restore all your data.  

You will need to restore the whole backup you took into a temporary development system, work out what you deleted and then script a way to export and import it into the main system.

[Oren]

Thanks for your warnings.

We already thought about using a temporary system, but not sure how can we find all the lost objects and import them back... Using just SQL looks much more appealing to me, but you say but might not be possible?

[Nic Brough -Adaptavist-]

SQL can help you identify what is missing, but you'll need to script the replacement if you don't want to manually re-enter it all.

[Oren]

SQL helped us see that the data was not deleted, and we figured Jira's re-index process that was not completed properly  (done by another team) mangled the Insight index. After some cleanup and Insight dedicated "Clean re-index" all looks well.

Thanks for your help.