Refreshing Test SQL Data with Production SQL Data

Introduction

Refreshing your test environment’s SQL data with production data is an important process to ensure accurate testing and development. Follow the steps below to achieve this seamlessly.

Step 1: Obtain Provisioning License Code

1. Log in to the ECM Website for the TEST environment.
2. Navigate to “admin” > “licensing.”
3. Capture the Provisioning License code; you’ll need it later in this guide.

Step 2: Backup ECM Databases in Both Environments

• Backup the following databases in both environments: a. AAAA0001 b. EclipseSignalRBackplane c. Astria_Host

Step 3: Restore Test Databases with Production Backups

• Restore the test databases using the production backups created in the previous step.

Step 4: Update Astria_Host Database

1. Open the Astria_Host Database.
2. Edit the top rows of the CompanyInstance table.
3. Under the “SERVER” column, update it with the SQL server name of the test environment. For instances, format it as SERVERNAME\INSTANCENAME if applicable.

Step 5: Clear Licensing using SQL Query

• Execute the provided SQL query to clear licensing information.

Step 6: Launch ECM Server Configuration Utility

1. Run the ECM Server Configuration Utility from the start menu (under the Eclipse Server folder).
2. Run the tool with a user account that has SQL permissions.
3. Navigate to the Index page, uncheck “Rebuild Index,” and complete the utility.

Step 7: Confirm Successful Configuration

• Launch the website and confirm successful login.

Step 8: License the Test Environment

1. Go to the admin tab and select “licensing.”
2. Enter the provisioning code received earlier.
   • If errors occur during licensing, contact Epicor support.
   • If no errors, your site is now up and running with updated database changes.

OPTIONAL

Copying Images from Production If you wish to copy images from production to the test environment:

1. Identify the image paths using the ECM configuration utility.
2. Copy the production image files to the test environment’s \DocPath directory.

SQL Query to Clear Licensing

USE [Astria_Host]
GO

DELETE FROM [dbo].[LicenseSession]
DELETE FROM [dbo].[LicenseUnlimitedConcurrent]
DELETE FROM [dbo].[LicenseNamed]
DELETE FROM [dbo].[Station]
DELETE FROM [dbo].[LicenseUsage]
DELETE FROM [dbo].[LicenseSeat]
DELETE FROM [dbo].[License]

SELECT [Name], ProvisioningCode FROM Company

ALTER TABLE dbo.License NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.LicenseSeat NOCHECK CONSTRAINT ALL
ALTER TABLE [dbo].[CompanyInstance] DROP CONSTRAINT [FK_CompanyInstance_Company]
ALTER TABLE [dbo].[License] DROP CONSTRAINT [FK_License_Company]

UPDATE dbo.Company SET ProvisioningCode = NULL
WHERE id = (SELECT companyid FROM dbo.CompanyInstance WHERE name = ‘AAAA0001’)

UPDATE dbo.Company SET Key1 = ‘00000000-0000-0000-0000-000000000000’,
Key2 = ‘00000000-0000-0000-0000-000000000000’,
Key3 = ‘00000000-0000-0000-0000-000000000000’
WHERE ProvisioningCode IS NULL

UPDATE dbo.CompanyInstance
SET CompanyId = NEWID()
WHERE name = ‘AAAA0001’

UPDATE dbo.Company
SET id = (SELECT companyid FROM dbo.CompanyInstance WHERE name = ‘AAAA0001’)

DELETE FROM dbo.License

ALTER TABLE [dbo].[CompanyInstance] WITH CHECK ADD CONSTRAINT [FK_CompanyInstance_Company]
FOREIGN KEY([CompanyId]) REFERENCES [dbo].Company ON DELETE CASCADE

ALTER TABLE [dbo].[CompanyInstance] CHECK CONSTRAINT [FK_CompanyInstance_Company]

ALTER TABLE [dbo].[License] WITH CHECK ADD CONSTRAINT [FK_License_Company]
FOREIGN KEY([CompanyId]) REFERENCES [dbo].Company ON DELETE CASCADE

ALTER TABLE [dbo].[License] CHECK CONSTRAINT [FK_License_Company]

ALTER TABLE dbo.License CHECK CONSTRAINT ALL
ALTER TABLE dbo.LicenseSeat CHECK CONSTRAINT ALL

SELECT [Name], ProvisioningCode FROM Company

Conclusion

By following these steps, you’ve successfully refreshed your test environment’s SQL data with production data. This process ensures that your testing accurately reflects the current state of the production environment, enhancing the quality of your development efforts.

Was this article helpful?

0 out of 5 stars

5 Stars		0%
4 Stars		0%
3 Stars		0%
2 Stars		0%
1 Stars		0%

5 Submit

Please Share Your Feedback

How Can We Improve This Article?

Name

Email

Submit