Who is this article for?

IT Administrators responsible for managing the system.

Services app and Server access are required.

Ideagen no longer provides guidance on use or configuration of the Retain applications. Please contact support at Retain International for further detail around restoring a Retain system.

It is occasionally necessary to restore the database.

This article describes the steps required in two common database restore scenarios:

• Restoring a database backup into its original environment, such as could be required in a DR situation
• Refreshing UAT from Production

1. Assumptions

This guide describes actions required within the application environment, it does not cover use of SQL Server.

It is assumed that your starting point is:

• Restorable database backup file.
• Configured App Manager instance.
• That you have a working knowledge of App Manager.

You will need admin rights for your SQL Server, the application server, and a working Identity for Ideagen Internal Audit (Aura) in the database you are wanting to restore.

2. Recovering a database back into its own environment

A typical situation might be as follows: you have had some kind of database server failure, and have the following available:

• a working application server.
• a now-working database server (if this is a replacement then we are assuming that it is named the same as the original).
• an up-to-date database BAK file, which you want to restore,
• iIf using Retain, a database BAK taken at the same point in time as Pentana.

In this case you will need to take the following steps:

1. Stop the Pentana Robot service.
2. Stop the Ideagen Internal Audit (Aura) application pools.
3. Purge the Microsoft Message Queue for the Production environment.
4. Restore the database.
5. If the restore is not overwriting an existing database (e.g. if this is a new server), it will probably be necessary to add a new login to the SQL Server. To avoid reconfiguration within Pentana this should be the same as was originally used (i.e. Windows account or SQL user/password), and the user added to the database will require db_datareader and db_datawriter rights.

If Retain is also in use, then the Retain database should be restored to the same point in time, and both systems should go back online together in order to stay in sync.

2.1. Consideration: Use of external FileStore

By default Ideagen Internal Audit (Aura) is configured so that file attachments are saved to the database.   If attachments are stored direct to a local volume or network share then it will be necessary to recover this area to the same point in time as the database to ensure consistency.

2.2. Consideration: Local database

If the client deployment’s local database was rebuilt during the time period between the main database backup and its restore then the local database will need to be rebuilt and redeployed:

1. Open App Manager.
2. Select the correct application instance.
3. Go to the [Config] tag and Rebuild Local DB.

This will update the location specified on the [Setup] tab (under Client > Local Database).  Where the system is configured for ClickOnce this will update the package, but if you deploy Ideagen Internal Audit (Aura) in another way then the client will need to be updated outside of App Manager per your normal method.

2.3. Consideration: Custom Robot jobs

If the system has custom import / export jobs then these will need to be taken into account to ensure data is consistent.

3. Restarting the system

Once the above is completed then the services can be restarted:

1. Start the Pentana application pools.
2. Start the Robot service.

3.1. Consideration: Checkout

If users have checked out data for offline working in the time period between the database being backed up and restored then the metadata registering this checkout will be absent in the restored system.  However, the clients themselves will still see the audits as checked out, and allow offline working that cannot then be checked in.

Therefore, end users should be contacted to verify that they didn’t check out during this time period.  If they did:

• Offline working should not be carried out, as work won’t be checked back in.
• Their individual offline client should be removed.
• Any offline work carried out will need to be rekeyed.

The situation is more complex if a user has one audit that was checked out before the database was backed up and one after (i.e. the system thinks they have just one offline) – in this case it will be necessary to check in the first audit and then remove the offline client, before rekeying the data for the second audit.

4. Refreshing UAT from Production

A common task is to take the current live/production (PRD) database and copy it into the UAT environment, in order to refresh the data.

Initially this puts you in a situation where you have two identical databases in use, meaning that you risk duplication of email notifications and a corruption of cache data unless additional steps are taken, as described below.

4.1. Refreshing the database(s)

1. Stop the Pentana Robot service for UAT.
2. Stop the Ideagen Internal Audit (Aura) application pools for UAT.
3. Purge the Microsoft Message Queue for UAT.
4. Restore the PRD database and rename, overwriting the UAT database.
5. If different SQL logins are used to access both PRD and UAT databases then the login for UAT will need to be added to the newly-restored database.

If Retain is also in use, then the Retain database should be restored to the same point in time, and both systems should go back online together in order to stay in sync.

4.2. Updating the System identity as required

If the IIS application pools used for PRD and UAT are different, then the UAT system will need the correct identity adding:

1. Open App Manager.
2. Select the UAT application instance.
3. Go to the Tasks tab.
4. Select Manage System Identities.
5. Update the Identity entries so that the correct app pool identities are shown as required.
6. Click Apply.

4.3. Rebuilding local database

It will normally be necessary to rebuild and redeploy the local database:

1. Go to the Config tab.
2. Click Rebuild Local DB.

This will update the location specified on the [Setup] tab (under Client > Local Database).  Where the system is configured for ClickOnce this will also update the package, but if you deploy Ideagen Internal Audit (Aura) some other way then the client will need to be updated outside of App Manager per your normal method.

4.4. Deactivating UAT email jobs

If Action Tracking emails are used then it is important to ensure that these are turned off in UAT, in order to avoid users receiving duplicate and increasingly inaccurate emails:

1. Start the Pentana application pools.
2. In App Manager, select the [Robot] tab.
3. Click Robot Service > Connect.
4. For each Robot job in the list relating to email, deselect ‘Active’.
5. Save.

4.5. Updating UAT email content

The body text of action tracking emails typically contains a hyperlink to the web application.  Normally this is in the form of a tag, but some organisations will specify a URL.  In order to remove PRD values from UAT:

Ensure the website tag is changed:

1. In App Manger, click Edit System Settings
2. Expand the Hyperlink section
3. Against Hyperlink.WebSite, change the value to match the UAT environment
4. Save
5. Click Edit System Settings to close the panel

Ensure action tracking emails do not contain hard coded URL.

In v5: For each email in the groups Action Tracking and Risk Management, go to the Job Parameter Body and check for hard coded URLs.

In v6: In the desktop application, for each of the following sections in Admin > System Settings, check for hardcoded URLs:  Action Updates, Entity Risks, Entity Controls, Controls, Audit Processes, Document Requests.

4.6. Updating UAT link suffix

The link suffix controls how the desktop application launches hyperlinked records.

To change this value, Ensure the website tag is changed:

1. In App Manger, click Edit System Settings.
2. Expand the Hyperlink section.
3. Against Hyperlink.VisionLinkSuffix, change the value to e.g. UAT.
4. Save.
5. Click Edit System Settings to close the panel.

4.6.1. Consideration: Use of external FileStore

By default Ideagen Internal Audit (Aura) is configured so that file attachments are saved to the database.   If attachments are stored direct to a local volume or network share then it will be necessary to copy the PRD store to that used for UAT to ensure consistency.

4.6.2. Consideration: Custom Robot jobs

If the system contains custom robot jobs that refer to file paths then these should be changed as required.

4.6.3. Consideration: DB recovery model

If your PRD and UAT databases are subject to different backup regimes then you may need to consider whether to change the UAT database recovery model, e.g. to set from Full to Simple.

4.7. Restarting the system

1. Restart the Pentana application pools
2. Start the Robot service

4.7.1. Consideration: Checked out audits

If a user has an audit checked out in the PRD environment, when this database is moved to UAT note that:

• The audit will still appear as checked out by them
• They will not be able to launch it offline in UAT, as a local offline instance for UAT does not exist

4.7.2. Consideration: Where email jobs are to be tested in UAT

If part of the UAT process is to involve testing of email jobs, it may be desirable to remove email addresses for all users of the system except those in particular who will be conducting testing.  This can be done through the main desktop application.  Once email addresses are removed / dummy email addresses are added, the notification jobs can be made active.

4.8. Scripting the changes

The settings changes described above can be scripted, which will reduce the effort involved in carrying out the task – particularly useful if this task is carried out repeatedly.  An example script is attached to this article, but note that editing (per the comments included) is required before it is used. 

4.9. Passwords

When logging into the newly-refreshed UAT system, bear in mind that user credentials will also have been copied.