Data migration between P4 environments

This guide provides a step-by-step process for migrating data between databases, most commonly from a Production (PROD) environment to a Test (TEST) or Development (DEV) environment. The migration ensures that data from the production system is accurately replicated into non-production environments for testing or development purposes, while maintaining proper configurations and avoiding disruptions to the live system.

The migration consists of two main steps:

1. Creating a Backup: Export the desired database into a backup file.

2. Restoring the Backup: Import the backup file into the target database.

These tasks are best performed using SQL Server Management Studio (SSMS).

Step 1: Creating a Backup

1. Right-click on the source database in SSMS and navigate to:
   Tasks → Back Up....

   

2. In the Back Up Database window:

   • Set Backup type to Full.

   • Ensure Backup component is set to Database.

     

   • In the Destination section, specify a new .bak file.

     

     • Save this file to a shared directory accessible to the target server.

3. Confirm by clicking OK. The backup process will create the .bak file.

   

   

Step 2: Restoring the Backup

1. On the target server, right-click on the target database in SSMS and select:
   Tasks → Restore → Database.

   

2. In the Restore Database window:

   

   • Select the backup file created in Step 1 as the source.

     

     

3. Adjust the restore options as required to fit your environment.

   

4. Click OK to apply the backup to the target database.

If missing after migration, User is needed to be connected with Login:

ALTER USER {DB user} WITH LOGIN {Login};

Step 3: Manual Data Adjustments

After restoring the database, some manual adjustments are necessary to adapt the data to the test environment.

Update Application URLs

Run the following queries on the test database to update the application URLs:

UPDATE frontend_application
SET link = REPLACE(link, 'PROD_URL', 'TEST_URL');

Update Import/Export URLs

Adjust the URLs for imports/exports to ensure they point to the correct environment:

API connection

UPDATE import_com_setting_api
SET host = REPLACE(host, 'TEST_URL', 'PROD_URL');

API Request U následující tabulky, pouze pokud obsahuje absolutní URL adresu:

UPDATE import_api_request
SET url = REPLACE(url, 'TEST_URL', 'PROD_URL');

Trans API Request U následující tabulky, pouze pokud obsahuje absolutní URL adresu:

UPDATE trans_api_request
SET url = REPLACE(url, 'TEST_URL', 'PROD_URL');

DB connection

UPDATE trans_com_setting_db
SET host = REPLACE(host, 'TEST_URL', 'PROD_URL');

FTP connection

UPDATE import_com_setting_ftp
SET host = REPLACE(host, 'TEST_URL', 'PROD_URL');

File connection - v případě, že se jedná o sdílenou složku

UPDATE import_com_setting_local_file
SET path = REPLACE(path , 'TEST_PATH', 'PROD_PATH');

Multimedia file

UPDATE multimedia_file
SET url = REPLACE(url, 'PROD_URL', 'TEST_URL');

OPC UA server

UPDATE opc_ua_server
SET endpoint_url = REPLACE(endpoint_url, 'PROD_URL', 'TEST_URL');

Printer

UPDATE printer
SET ip = REPLACE(ip, 'PROD_IP', 'TEST_IP'), 
    port = REPLACE(port, 'PROD_PORT', 'TEST_PORT');

Backgorund job - Production item assign

UPDATE prod_item_background_assign
SET url = REPLACE(url, 'PROD_URL', 'TEST_URL');

Backgorund job - Production item creation

UPDATE prod_item_creation_queue
SET url = REPLACE(url, 'PROD_URL', 'TEST_URL');

Backgorund job - Production item deletion

UPDATE prod_item_delete_queue
SET url = REPLACE(url, 'PROD_URL', 'TEST_URL');

Screen splitter

UPDATE scrn_split_app
SET url = REPLACE(url, 'PROD_URL', 'TEST_URL');

Settings

UPDATE setting SET value = '{{environmentTitle}}' WHERE parameter = 'environment_title';
UPDATE setting SET value = REPLACE(value, 'PROD_URL', 'TEST_URL') WHERE parameter = 'websockets_endpoint';
UPDATE setting SET value = REPLACE(value, 'PROD_URL', 'TEST_URL') WHERE parameter = 'host';
UPDATE setting SET value = REPLACE(value, 'PROD_VALUE', 'TEST_VALUE') WHERE parameter = 'smtp_host';
UPDATE setting SET value = REPLACE(value, 'PROD_VALUE', 'TEST_VALUE') WHERE parameter = 'smtp_username';
UPDATE setting SET value = REPLACE(value, 'PROD_VALUE', 'TEST_VALUE') WHERE parameter = 'smtp_password';
UPDATE setting SET value = REPLACE(value, 'PROD_VALUE', 'TEST_VALUE') WHERE parameter = 'sso_authorization_ldap_host';
UPDATE setting SET value = REPLACE(value, 'PROD_VALUE', 'TEST_VALUE') WHERE parameter = 'sso_authorization_ldap_login';
UPDATE setting SET value = REPLACE(value, 'PROD_VALUE', 'TEST_VALUE') WHERE parameter = 'sso_authorization_ldap_pswd';

Queue Email

UPDATE queue_email SET sent = 1;

Example URLs:

• Production: https://productionserver.com

• Test: https://testserver.com

Additional Notes and Precautions

1. User Permissions:

   • Do not modify users' default schema.

   • Avoid assigning sysadmin roles unnecessarily.

2. Credentials:

   • Avoid changing database login passwords to prevent authentication issues.

3. Services:

   • Ensure services like Apache and MSSQL remain active during migration.

4. Configurations:

   • Avoid modifying configuration files like httpd.conf or hosts.