Restoring a Jiwa database to a different SQL Server

There may be occasions when you wish to restore your Jiwa database to a different SQL Server. It may be part of a disaster recovery process, or migrating to new hardware or environments.

This article assumes SQL Server has already been installed and configured on the new SQL Server.

Step-by-step guide

1. Restore the database
   1. Using SQL Server Management Studio, right click on the databases node under the SQL Server instance and select Restore Database... from the context menu.
      
   2. The Restore Database dialog then appears, Select Device as the source and press the ellipses button to the right
      
   3. The Select backup devices dialog then appears. press the Add button
      
   4. The Locate Backup File dialog then appears - locate your SQL backup file and press the OK button.
      
   5. The Select backup devices dialog is now in focus again, press OK
      
   6. The Restore Database dialog returns to focus - at this point you can opt to change the name of the database that will be created from the backup by changing the Database name in the Destination section of the dialog
      
   7. Press OK and the restore will begin. At the conclusion of the restore, a confirmation dialog appears:
      
2. Create SQL Logins

   1. There are 3 SQL Logins which need to be created with a known password. In a later step you will change this password to your own. Run the following script against the master database to create the SQL logins:

      Create SQL Logins

      CREATE LOGIN JiwaLogin WITH Password = 'JiwaApplicationLogin123'
      CREATE LOGIN JiwaUser WITH Password = 'JiwaFinancials123'
      CREATE LOGIN JiwaReports WITH Password = 'JiwaFinancials123'

3. Recreate SQL Users in the database

   1. While restoring the database did create the required SQL database users, they are associated with the SQL Logins SID from the old SQL Server. We need to drop and re-create the SQL Users in the restored database and also grant the necessary permissions. Run the following SQL Script to do this:

      Drop and Recreate SQL Users

      USE <ENTER DATABASE NAME HERE>
      
      DROP USER JiwaLogin
      DROP USER JiwaUser
      DROP USER JiwaReports
      GO
      
      CREATE USER JiwaLogin FOR LOGIN JiwaLogin
      CREATE USER JiwaUser FOR LOGIN JiwaUser
      CREATE USER JiwaReports FOR LOGIN JiwaReports
      GO
      
      usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
      GO
      usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
      GO
      
      --For 7.0.175 and earlier
      --GRANT_ALL_USER_TABLES
      --GO

4. Script all Jiwa user passwords to known value

   1. Jiwa stores the Jiwa password and SQL Login and SQL Password encrypted in the SQL Table HR_Staff.  As the recommended practice is to change the JiwaUser and JiwaReports SQL Login password to be a secret value, it is likely the encrypted values in the now restored database do not match the values set above in step 2a. Run the following SQL Script to reset the values for the Admin Jiwa user to be the expected values.

      Reset Jiwa and SQL credentials

      UPDATE HR_Staff SET Password = '60A889064BAD474508723B9200184D09',
      	PasswordLastChangedDateTime = '2014-05-28 11:20:48.000',
      	LastSavedDateTime = '2014-05-28 11:20:48.000',
      	SQLLogin = '6B4A7C2B2E902402268692E8932CDB50',
      	SQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
      	ReportSQLLogin = '887BFFE23AB10B3C5412655265278A11',
      	ReportSQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
      	SQLLoginLastSavedDateTime = '2014-05-28 11:20:48.000',
      	ReportsLoginLastSavedDateTime = '2014-05-28 11:20:48.000'
      WHERE Username = 'Admin'
      GO

5. Recreate Jiwa connections
   1. Each Jiwa client application may now need to have a new connection created, or an existing connection adjusted to use the new SQL Server name. See Creating a connection for steps on creating a connection.
6. Login to Jiwa and set new SQL Passwords
   1. In step 4a. we set the Jiwa password for the user Admin to be 'password' - and the SQL credentials to be a known value. Now we must set the Jiwa password for the Jiwa Admin user to be a new, private value, as well as the SQL credentials. Log in to Jiwa, connecting to the newly restored database
      
   2. Open the Staff Maintenance form (Located under the menu System Settings → Staff Configuration → Staff Maintenance) and locate the Admin user. On the Main tab, press the Password... button to reset the password.
      
   3. On the Database Login tab, enter a new value for the SQL Password and Reports SQL Password fields.  Make a note of these new password values as they are needed in the next step. Press the Save button on the Ribbon when finished.
      
7. Change SQL Login passwords

   1. Using SQL Management Studio, Run the following SQL Script to reset the SQL Login passwords to be the values used in the previous step.

      Reset SQL Login passwords

      ALTER LOGIN JiwaUser WITH Password = 'MyNewPassword'
      ALTER LOGIN JiwaReports WITH Password = 'AnotherPassword'

      The JiwaUser SQL Login password maps to the "SQL Login" field of the Staff Maintenance form, and the JiwaReports SQL Login password maps to the "Reports SQL Login" field of the Staff Maintenance form.

Related articles

• Page:
  Changing the standard Jiwa SQL Login and Report SQL Login Users
• Page:
  Improve database performance
• Page:
  Restoring a Jiwa database to a different SQL Server
• Page:
  Scheduled Database Backup & Restore
• Page:
  How do I find out what tables are using the most space in the database