How can I create a copy of my current Production Footprints 12.1.X - 20.XX system for testing purposes?

A backup of the Footprints 12 Database is required for a proper working test system..  A FootPrints File Systems backup corresponding with the database backup is needed for a full duplication of the environment.
NOTE: This process requires the production Footprints 12.1.X - 20.XX system have a System Administrator user that is configured to use "Footprints Authentication".  The recommended best practice is to ensure at least one system administrator user have Footprints Authentication in case LDAP Authentication fails.  This account will still have access login to Footprints 12.1.X - 20.XX to reset LDAP settings.  As part of the copy process below, the LDAP Authentication is disabled due to the new pass phrase used for the test server.

For instructions on how to backup see 000011024 How to Backup FootPrints Service Core 12 (for Disaster Recovery purposes)

The instructions below can be used to restore on a server that has already had Tomcat appropriately installed and configured.
Please consult the Knowledge Base articles below if Tomcat assistance is needed prior to recovering Footprints 12.1.X - 20.XX.
000011063 How to install Tomcat for FootPrints Service Core version 12
000010914 How to configure Tomcat for FootPrints Service Core

These instructions are written for a Windows installation of FootPrints Service Core using a Microsoft SQL database.  Although the basic process is the same for Linux and PostgreSQL, if assistance is needed, please contact FootPrints Service Core Technical Support.

1. Restore the database to an existing Microsoft SQL server. 
2. Once the database is restored, open a new Query Window to run the query below to identify the specific FootPrints Service Core 12 version needed for the recovery. 

   select * from fpscdb001_system.deployment_update order by start_datetime desc;

   NOTE: If the restored database has a different schema than specified above, please alter the fpscdb001 portion of the query accordingly.
3. In the results of the query, identify the proper version from the prod_ver column at the top of the results.   This version of FootPrints Service Core 12 should be used to install a clean copy on your new FootPrints Service Core server.  If the error column shows only NULL values, this is a good candidate database for recovery.  If any errors exists, please contact Technical Support for assistance.
4. Download the version identified above for installation on your new FootPrints Service Core server.   Install FootPrints 12.1.X - 20.XX as normal selecting the same SQL Server that the existing database had been restored to.   When asked choose “Allow installer to create database” DO NOT select “Use existing database” as this will cause undesired results.

   

5. Before finishing the interactive portion of the installer has finished you will get a summary page showing all of the settings including your Passphrase, database users & passwords and new Footprints System Administrator Account.  Please copy and save this data into a text file for later use.
6. Once the installation of FootPrints 12.1.X - 20.XX is complete, start Tomcat and login with your System Administrator account to load your license file.  There is no need to create any containers or use the Business Process Templates, so you can just close this window when it appears.  Your database recovery is now ready to begin.
7. Stop the Apache Tomcat service
8. Before we can continue we need to determine if new SQL Logins will be needed for the restored database by viewing the restored database’s users from SQL Management Studio.  Expand the database’s Users list and if these match the SQL instances security Logins created with the new installation, skip ahead to step 9.  If these Users do not match those created by the installation, use the SQL query below to create new SQL Logins to match those Users from the restored database.

   

   -- If the restored database has different SQL Users than the Logins that were created with your new installation, this script will create them.
   -- These passwords are install-specific and should be replaced with those used by your new local installation.  The login name should be altered 
   -- to match those in the restored database and the DEFAULT_DATABASE name should be changed to the name of your restored database.
   USE [master]
   GO
   CREATE LOGIN [fpscdb001_adm] WITH PASSWORD='UqZy#p7S8n52op',
   DEFAULT_DATABASE=[fpscdb001], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   CREATE LOGIN [fpscdb001_rpt] WITH PASSWORD='UqZy#p7S8n52op',
   DEFAULT_DATABASE=[fpscdb001], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   CREATE LOGIN [fpscdb001_sec] WITH PASSWORD='NjJwY0Dz#g$tgh',
   DEFAULT_DATABASE=[fpscdb001], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   CREATE LOGIN [fpscdb001_usr] WITH PASSWORD='RPgAoPEp$91i09',
   DEFAULT_DATABASE=[fpscdb001], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

9. In your SQL server we will need to prepare your restored database for recovery using the SQL Queries below.  NOTE: As with step 8 above, if your restored database has a different schema than that created with the new installation, please alter these queries accordingly.

   Please See attached Script. We now have 2 scripts. One for 12.1.03 and below one for 12.1.04 and above.
   

10. If the restored database uses the same schema name (ex fpscdb001) as the newly installed Footprints instance, skip ahead to step 11.  If the schema names are different, we will need to perform a Find/Replace on the entire X:\Program Files\BMC Software\FootPrints Service Core directory so that all references to the new installs schema are changed to the expected schema for the restored database.  Using Notepad++ or another application to make the change, ensure you have FULL ADMINISTRATOR rights to the files by running the edit application as administrator, or the change will not take place.
11. In your test installation of FootPrints Service Core 12 locate your installed directory.   The KB Article 000011024 had you backup the Conf and Repository folders.   Before restoring them, rename your current folders, DO NOT DELETE THEM.
12. Restore your backed up Conf and Repository folders.   This may take some time as the repository folder contains all of your ticket attachments for the entire system.
13. After the folder restore, copy the two files below from your renamed Conf folder to replace the files in your restored folder.
    • footprints-environment.properties
    • log4j.properties
14. Next we need to manually edit the x:\Program Files\BMC Software\FootPrints Service Core\conf\footprints-environment.properties file to ensure the usernames and TenantID have been updated.

    SystemLocaleCode=en_US
    SystemLocaleName=English - United States
    EncryptionStrength=128
    Data0=7vjRdp5nc6ztNKnv57mA-s8RNk_0UxzKxED7YuBeZlIROSyIXdzi4nIwdzFThVUk2yyPZW5_x5Ye|qY9E3TS-GvTtqYMgMZXY2ce-aE5JzXgNae8XfZSIxPA1iGw70JZW0mBRdGqfV4u0DR7_deXzgA|
    Data1=QX2tGH5V7_LtZLa3Z6dAzGhMnUIIJRIX6aDa_Xuf3ZQ98rC7hLt9t7qtNFhMZM0J|
    Data2=JR02dTVkcGpubmZ2cmVtMDZqZGhoMDBlYzFmNWo1dnBsdG40dGxkNTV1MGhscmFoZjIzcTBqbjY4|OTZqMm1tZnZ1ODloY3IyY2JhNjM3bjM4bDFkaDN2ZzJhZjIwNXB0ZjA|
    DatabaseDriverClass=net.sourceforge.jtds.jdbc.Driver
    DatabaseConnectionUrl=jdbc:jtds:sqlserver://localhost:1433/Footprints
    GrantUsers=fpscdb001_usr
    DatabaseUserName=fpscdb001_usr
    DatabaseUserPassword=VVbIHlthKQMMPvUGz0I1A4bPbTfBUsvMSLhyheLGYm8HjbHLZAZ9cMW4YgwpPi_d|
    DatabaseAdminName=fpscdb001_adm
    DatabaseAdminPassword=mfUHse4tMuW29d_2VZooHe1a51SGnOWm5e56kLb2oKw4r59Ar0Wags8EtFP_XTvp|
    ReportingUserName=fpscdb001_rpt
    ReportingUserPassword=EB676BmUh9iWgA0llNJDu-b89X_VQ5TdTTRJXq3kULFW3PbSt9GydWXbpbrMUVuY|
    SecureUserName=fpscdb001_sec
    SecureUserPassword=p7uRChNfoY2U4p4CK7bGPhN5lzbvuvEnxk97mHKMM4cQTo-mbr3F4Be4nRJM7m5w|
    DatabaseStaticMappingsLocation=C:\\Program Files\\BMC Software\\FootPrints Service Core/conf/mappings
    TenantId=fpscdb001

15. Next update the database name in the DatabaseConnectionUrl= entry to that of the restored database.  The format of the string after “…:sqlserver://” should appear as below.   The highlighted instance portion can be removed if no named instance is being used.

    <hostname>:<port>/<databasename>;instance=<sql_instance_name>

16. Once the Footprints 12.1.X - 20.XX installation is pointed to the restored database, delete all current Tomcat and Footprints 12.1.X - 20.XX logs.  Start the Apache Tomcat service. 
17. When the login page displays use the query below to identify the System Admin users.  If any are set to FootPrints Internal Authentication they can be used to login now with their normal passwords.  Any that use External Authentication methods will not work at this time.  If none are able to login, please contact the FootPrints Service Core support team for assistance.

    select app_user_id,user_login,delete_date from fpscdb001_system.app_user where app_user_id in
    (select app_user_id from fpscdb001_system.app_user_prof_link where user_prof_id in
    (select user_prof_id from fpscdb001_system.user_prof where app_role_id in
    (select app_role_id from fpscdb001_system.app_role where app_role_name='System Administrator' and is_built_in='1')))

18. Once you can login successfully you will need to re-enter any external system credentials into Footprints to re-encrypt the passwords using the new installation’s key.  This includes:
    • LDAP Authentication credentials
    • LDAP Address Book credentials
    • SQL Address Book credentials
    • Any credentials in the “Integration“ administration menu
    • Any SQL Lookup configuration with your item’s Fields list using the “Manage Linked Fields” button
19. Edit the Incoming Email (System) settings and remove all mailbox configurations.  If incoming email testing is required, please use separate testing accounts to avoid the test system intercepting email meant for the production system.
20. Edit the Notifications (System) settings as well to repair the server information then add an Email Address Filter for "Only Send" and specify your own personal address.  This will prevent the test system from sending email to other users based on the existing data in the database.
21. Restart the Tomcat Service once more and login when the FootPrints Entrance Page is presented.  If login is successful, Footprints should now be ready for testing.

Using your test server as a Staging Server:

Troubleshooting:

Review the C:\Program Files\BMC Software\FootPrints Service Core\logs\footprints.log or the C:\Program Files\Apache Software Foundation\Tomcat 7.0\logs\catalina.*.log file for errors relating to starting Apache Tomcat and Footprints with the new configuration and database settings.

Research the errors found and contact BMC Software Footprints support if you cannot find a solution.

Common errors: The padding is not supported or not available
WARN |2018-01-05T11:19:38.660|EST|ServerName|localhost-startStop-1|org.apache.catalina.core.StandardContext.listenerStart()|||Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'authenticationManagerPostConstructionListener': Invocation of init method failed; nested exception is com.numarasoftware.footprints.infrastructure.utility.common.EncryptionUtilsException: The padding is not supported or not available.|

Stop the Apache Tomcat service, and run the SQL query against the restored database.

-- Set encrypted password values to null 
UPDATE fpscdb001_system.cfg_setting_val
SET string_val = NULL
WHERE cfg_setting_val_id in (
    SELECT csv.cfg_setting_val_id
FROM fpscdb001_system.cfg_setting_val csv
join fpscdb001_system.cfg_setting cs on cs.cfg_setting_id = csv.cfg_setting_id
WHERE 
  cs.cfg_setting_const like '%password%'
  and csv.string_val is not null
  and csv.string_val <> '');