This article describes how to move the FootPrints 12.X or 20.X  MS SQL Server database to a different database server. This article is specific to FootPrints installations using Microsoft SQL Server.

These instructions require the database passwords and/or passphrase value used during the installation of FootPrints .   Please contact FootPrints Technical Support If this value is not known.

The following information provides guidance related to the issue indicated in the title. For help understanding the general concepts, features, and navigation of version 12, consult the product Help system.

Additionally, these steps must be performed with full Administrator privileges.

1. Stop the  Tomcat Service- This should be listed as Apache Tomcat  x.x (under Start -> Administrative Tools -> Services).
2. Backup the source FootPrints  database using the following steps:
   1. Open up Microsoft SQL Server Management Studio (from either the source or target SQL server).
   2. Connect to the source SQL server where the Footprints database exists that is to be moved via backup/restore.
   3. Expand "Server Name -> Databases -> fpscdb001"
   4. Right-click "fpscdb001".
      1. Select "Tasks"
      2. Now select "Back Up..."
   5. Select FULL backup and choose a location to hold this backup.
      1. The default name will be fpscdb001.bak
      2. For example: C:\Program Files\Microsoft SQL Server\MSSQL.10.MSSQLSERVER\MSSQL\Backup\fpscdb001.bak
   6. Press the OK button to begin the backup.
3. Once the database backup successfully completes, copy fpscdb001.bak from the location it was backed up to on the source SQL server to a suitable location on the target SQL server.
4. Restore the database to the target SQL server using the following steps:
   NOTES:
   • The database name on the new database server does not need to be the same as the database name on the old database server.
   • The target SQL Server database server must be the same or later version of SQL Server as the source SQL Server database server.
   1. Connect to the target SQL server using the existing Microsoft SQL Server Management Studio console.
   2. Expand "Server Name -> Databases"
   3. Right-click Databases and select Restore Database....
   4. In the Restore Database window, do the following:
      1. Enter "fpscdb001" in the "To database" field
      2. Click on the radio button for "From Device"
      3. Click on the square button on the right.
      4. Click the "Add" button and select the location/path to the fpscdb001.bak file.
      5. Click OK
5. Create the four (4) required FootPrints database logins on the new SQL Server.
   IMPORTANT: The database logins must have the exact same names as the users in the restored database. Use SQL Server Management Studio to find the names of the users in the database.
   The user names can be found by accessing SQLservername (root, top level) -> Databases -> fpscdb001 -> Security -> Users
                                              
6. Once the SQL user names are determined, create SQL login names that are identical to the 4 user names, using the following steps:
   1. In SQL Server Management Studio on the target server, access SQLservername (root, top level) -> Security
   2. Right click on Logins
   3. Choose New Login...
   4. Enter fpscdb001_adm (or the login name for the "adm" account found in the previous steps) in the Login name: field.
   5. Choose the SQL Server authentication option.
   6. Provide the password used during the original Footprints installation and confirm it. Be sure to make a note of the password as it may be needed in step 8 below.
   7. Uncheck, or deselect the options for Enforce password policy, Enforce password expiration and User must change password at next login
   8. Select fpscdb001 (or the database that was restored) for Default database.
   9. Select English (or the appropriate language) for Default language.
   10. Press the OK button to create the Login.
   11. Repeat steps a-j to create a Login for each of the fpscdb001_sec, fpscdb001_usr, and fpscdb001_rpt users. 
7. Link the newly created SQL logins to the existing users in the restored database:
   1. In SQL Server Management Studio, run the following SQL statements, changing the name of the database if different from the default of "fpscdb001", and the user/login names if different from the defaults (seen inside [ ] brackets below):

      USE FPSCDB001
      ALTER USER [fpscdb001_adm] WITH LOGIN = [fpscdb001_adm]
      ALTER USER [fpscdb001_rpt] WITH LOGIN = [fpscdb001_rpt]
      ALTER USER [fpscdb001_sec] WITH LOGIN = [fpscdb001_sec]
      ALTER USER [fpscdb001_usr] WITH LOGIN = [fpscdb001_usr]

   2. Execute the statement by pressing the F5 key, or press the "!Execute" button on the toolbar in SQL Server Management Studio.

      NOTE: If BOTH the FootPrints application AND database are being moved AND the same database user/login names and passwords for those 4 users will be used on the new database server, Steps 8 and 9 below do not need to be followed. If the FootPrints application is NOT being moved (only the db is being moved), use the Original Footprints installation database passwords.
      Additionally, if the passwords for the 4 database users are not known or are being intentionally changed, Steps 8 and 9 below MUST be performed. 

8. Encrypt the database user passwords and save them in the footprints-environment.properties file.
   
   NOTE: If steps have already been performed in article 000011113 to move the FootPrints application prior to performing the steps in this article to move the database, when providing the passphrase below (see "Your Passphrase Here"), be sure to use the passphrase created during the install of FootPrints on the old server.
   1. On the computer where FootPrints is installed run the following command once for each of the four database users to encrypt each users password.
      • Replace the path values in the command with the location where FootPrints is currently installed (i.e. the "base install folder" - C:\Program Files\BMC Software\FootPrints Service Core).
      • Replace the "Your Passphrase Here" value with the passphrase value used during the installation of FootPrints. Additionally, be sure to include doublequote characters around the passphrase as seen in the example below, but do not use the doublequote character as part of the passphrase, as errors will occur when attempting to do so.
      • Replace the "passwordToBeEncrypted" value with the plain text password to be encrypted.

      An example command is:

      java -cp "C:\Program Files\BMC Software\FootPrints Service Core\web\WEB-INF\lib\*;C:\Program Files\BMC Software\FootPrints Service Core\conf" com.numarasoftware.footprints.tool.EncryptionTool -passphrase "Your Passphrase Here" -text passwordToBeEncrypted

      NOTE: If Java is not in the system path, provide the full path to the java executable, for example:

      "C:\Program Files\Java\jre1.8.0\bin\java.exe" -cp "C:\Program Files\BMC Software\FootPrints Service Core\web\WEB-INF\lib\*;C:\Program Files\BMC Software\FootPrints Service Core\conf" com.numarasoftware.footprints.tool.EncryptionTool -passphrase "Your Passphrase Here" -text passwordToBeEncrypted

       
   2. The command will return the encrypted password in the "Result" field of the command's output: 

                     
   
   NOTE: Certain output will be shown in the command prompt window when the command begins to run, which may appear to be errors and indicating failure as shown in the image immediately below. As long as the last 3 lines of the output show the "Result:", the provided password (at the line that contains "Verified:"), and the "Strength: 128" line, the "errors" shown in the image can safely be ignored. 
   
                     
   
   An error message which says, "Error: Could not complete the requested operation.", means the passphrase provided is incorrect. 
                     

9. Copy the encrypted password value and paste it into the appropriate location in the C:\Program Files\BMC Software\FootPrints Service Core\conf\footprints-environment.properties file. The red arrows in the image below point to the proper locations to enter the encrypted passwords: 

                                 
   Be careful to copy each password to the correct location in the footprints-environment.properties file.
   
   This table shows the default mapping of database login names to entries in the footprints-environment.properties file.

    

   Database Login name	User name key in properties file	Password key in properties file
   fpscdb001_usr	DatabaseUserName	DatabaseUserPassword
   fpscdb001_adm	DatabaseAdminName	DatabaseAdminPassword
   fpscdb001_rpt	ReportingUserName	ReportingUserPassword
   fpscdb001_sec	SecureUserName	SecureUserPassword

   NOTE: The database login names may be different than those displayed in the table above. The actual database login names must match the database user names in the restored FootPrints Service Core database.
10. Update the database connection information at the line that begins with "DatabaseConnectionUrl=" in the footprints-environment.properties file to use the newly restored FootPrints Service Core database as seen in the image below: 

                
      Example:

    DatabaseConnectionUrl=jdbc:jtds:sqlserver://<hostname>:<port>/<databasename>;instance=<sql_instance_name>
    DatabaseConnectionUrl=jdbc:jtds:sqlserver://CLM-xxx-00000:1433/fpscdb001;instance=FOOTPRINTS
    

11. Start the Tomcat Server. FootPrints Service Core will now be using the restored database on the new SQL Server.