Dynamics GP: Create a Test Company with Live Data Copy for Safe Experimentation

Table of Contents

Creating a test company in Dynamics GP is a fundamental task for administrators and consultants. A test environment allows for safe experimentation with new features, customizations, integrations, and troubleshooting issues without risking critical live data. While a new, empty company can be useful for some tests, a copy of your live company data provides the most realistic environment for many scenarios. This process involves backing up your production database, restoring it with a new name, and then running specific scripts to ensure Dynamics GP recognizes the new database correctly as a separate company.

Utilizing a copy of live data is invaluable for testing upgrades, applying hotfixes, validating new business processes, training users on real-world scenarios, and diagnosing issues that only occur with specific data conditions. It provides a sandbox where you can make significant changes, run potentially destructive processes, or develop reports and integrations using data structures and content identical to your live system. This minimizes the risk of unexpected errors or data corruption in your production environment.

Why Create a Test Company with Live Data?¶

The primary purpose of a test company populated with live data is to provide an environment that mirrors your production system as closely as possible. This allows for testing activities that would be too risky to perform directly on your live company. Common use cases include:

• Testing Upgrades and Patches: Before applying a Dynamics GP upgrade or a specific patch to your live system, you should always test it on a copy of your live data. This helps identify potential compatibility issues with third-party products, customizations, or specific data structures before they impact production.
• Validating Customizations and Integrations: Developing and testing new reports, Dexterity customizations, or integrations with external systems requires a realistic dataset. A test company with live data ensures that these developments function correctly with the actual data volume and complexity found in production.
• User Training: Training new users or demonstrating new modules is significantly more effective when using data they are familiar with. A test company allows users to practice transactions and navigate the system using data that reflects their daily work, without the fear of making mistakes in the live system.
• Troubleshooting Specific Issues: Sometimes, a bug or performance issue is data-specific. Having a copy of the production data allows technical staff to troubleshoot these complex problems in an isolated environment without impacting ongoing business operations.
• Reporting and Analysis Development: Building complex SQL queries or reporting services reports often requires testing against a full dataset to ensure accuracy and performance.

Without a realistic test environment, organizations risk encountering significant, and potentially costly, problems when deploying changes or troubleshooting issues directly in their live system. The effort involved in creating and maintaining a test company is a crucial investment in the stability and reliability of your Dynamics GP implementation.

Prerequisites and Planning¶

Before you begin the process of creating a test company from a live data copy, ensure you have the necessary prerequisites and have completed some basic planning:

• SQL Server Access: You need administrative access to the SQL Server instance hosting your Dynamics GP databases. This includes permissions to back up databases, restore databases, and execute SQL scripts.
• Dynamics GP Utilities Access: You will need access to launch and use Dynamics GP Utilities, typically with a user account that has administrative privileges within GP.
• Sufficient Disk Space: Restoring a database requires disk space equal to or greater than the size of your live company database file (.MDF) and transaction log file (.LDF). Ensure the drive where you plan to restore the database has ample free space. Consider the size of your live database; large databases require significant disk resources for the test copy.
• Timing: The backup process can take some time, depending on the size of your live database. The restore process also requires time. Plan this activity during a period of low system usage or scheduled maintenance to minimize impact on your production system. It is generally recommended to perform the backup when users are not actively posting or making significant data changes.
• Naming Convention: Decide on a clear naming convention for your test company database and the logical company name within Dynamics GP. For example, if your live database is TWO and the company name is “The World Online, Inc.”, you might name your test database TWOTEST and the company name “The World Online, Inc. (TEST)”. Consistency helps differentiate test environments from live ones.

Proper planning ensures the process runs smoothly and avoids potential issues related to permissions, disk space, or impacting live operations.

Step-by-Step Guide¶

Creating a test company with live data involves several distinct steps, starting with preparing the data source and ending with configuring the new test environment.

Step 1: Back Up the Live Company Database¶

The first step is to create a full backup of your live Dynamics GP company database using SQL Server Management Studio (SSMS). This backup will serve as the source data for your new test company.

1. Open SQL Server Management Studio and connect to the SQL Server instance hosting your Dynamics GP databases.
2. Expand “Databases” and locate the live company database you want to copy.
3. Right-click on the database, navigate to “Tasks”, and select “Back Up…”.
4. In the “Back Up Database” window, ensure “Backup type” is set to “Full”.
5. In the “Destination” section, note the location where the backup file will be saved or click “Remove” and “Add” to specify a different location and file name. Ensure the backup file name is descriptive (e.g., TWOLive_FullBackup_YYYYMMDD.bak).
6. Click “OK” to start the backup process. Monitor the progress in the bottom left corner of the SSMS window.
7. Verify that the backup completed successfully. You should see a “Backup of database ‘YourLiveDBName’ completed successfully” message.

Having a recent and successful full backup is critical. This is the snapshot of your data that will populate the test environment.

Step 2: Restore the Backup to a New Database Name¶

Next, you will restore the backup file you just created, but importantly, you will restore it as a new database with a different name. This is how you create the separate database for your test company.

1. In SQL Server Management Studio, right-click on the “Databases” folder and select “Restore Database…”.
2. In the “Restore Database” window, under the “Source” section, select “Device” and click the browse button (…).
3. In the “Specify Backup” window, click “Add”, navigate to the location where you saved the backup file in Step 1, select the .bak file, and click “OK”. Click “OK” again in the “Specify Backup” window.
4. Under the “Destination” section, the “Database” field will automatically populate with the original database name (your live company name). Crucially, change this field to the desired new name for your test company database (e.g., TWOTEST).
5. In the “Select a page” pane on the left, select “Files”. You will see the original file names and locations for the data (.mdf) and log (.ldf) files. You must change the “Restore As” file names to match your new database name (e.g., change YourLiveDBName.mdf to TWOTEST.mdf and YourLiveDBName_log.ldf to TWOTEST_log.ldf). You may also need to change the folder paths if you want to store the test database files in a different location. It is highly recommended to change these file names to avoid conflicts.
6. In the “Select a page” pane, select “Options”. Check the box for “Overwrite the existing database (WITH REPLACE)”. Note: Since you are restoring to a *new database name that shouldn’t exist yet, this option is technically not strictly necessary for the first time creating the test company with this name. However, it’s good practice to enable it, especially if you ever plan to refresh this test company by restoring over the existing test database.* Also, ensure “Close existing connections to the destination database” is checked if the test database already exists and users might be connected (relevant for refreshing, not initial creation).
7. Click “OK” to start the restore process.
8. Monitor the progress. Once complete, you should see a “Database ‘YourTestDBName’ restored successfully” message.

You now have a new database on your SQL server containing a copy of your live data. However, Dynamics GP is not yet aware of this new database as a company, and the internal company identifiers within the database still reflect the original live company.

Step 3: Create the Test Company Entry in Dynamics GP Utilities¶

This step involves using Dynamics GP Utilities to register the new database as a company within the Dynamics GP system dictionary (the DYNAMICS database).

1. Launch Dynamics GP Utilities. You typically find this in the same program group as Dynamics GP itself.
2. Log in using your Dynamics GP system administrator credentials (usually ‘sa’).
3. Utilities will check the system database. If it prompts you to update, proceed with the update if necessary, though usually, for just adding a company, this isn’t needed.
4. On the “Welcome to Utilities” screen or a similar initial screen, look for an option related to “Company”. Select “Create a company database”.
5. Click “Process”.
6. On the “Create Company” window:
   • Company Name: Enter the logical name for your test company that users will see when logging into GP (e.g., “The World Online, Inc. (TEST)”).
   • Database Name: This is crucial. Enter the exact name of the database you restored in Step 2 (e.g., TWOTEST). This name must match the physical database name on the SQL server.
   • Server Name: Verify or select the correct SQL Server instance where the new database resides.
7. Click “OK”.
8. Utilities will now perform several actions:
   • It checks if the database name exists. It should find it because you restored it in Step 2.
   • It will update the SY01500 table in the DYNAMICS database to register the new company name and map it to the specified database name and a unique internal CMPANYID.
   • It may run some initial setup scripts, but it won’t populate the tables, as they are already populated from your restore.

After this step, Dynamics GP knows about the new test company database and assigns it a unique internal COMPANYID. However, the data within the test database still contains the original COMPANYID and INTERID values from the live company. This leads to inconsistencies.

Step 4: Update COMPANYID and INTERID in the Test Company Database¶

This is perhaps the most critical technical step. Dynamics GP stores company identifiers (COMPANYID, CMPANYID, INTERID, DB_NAME, DBNAME) in various tables. When you restore a database copy, these identifiers within the data tables still point to the original live company’s details. The script provided below updates these values throughout the newly restored test database to match the new COMPANYID and INTERID assigned by GP Utilities in Step 3.

You must run this script against the new test company database, not the DYNAMICS database or the live company database.

if exists
(
   select
      1
   from
      INFORMATION_SCHEMA.COLUMNS
   where
      TABLE_NAME = 'SY00100'
)
begin
   declare @Statement varchar(850)
   select
      @Statement = 'declare @cStatement varchar(255)

      declare G_cursor CURSOR for
      select
         case
            when
               UPPER(a.COLUMN_NAME) in
               (
                  ''COMPANYID'',
                  ''CMPANYID''
               )
            then
               ''
               update
                  '' + a.TABLE_NAME + ''
               set
                  '' + a.COLUMN_NAME + '' = '' + cast(b.CMPANYID as char(3))
               else
                  ''
                  update
                     '' + a.TABLE_NAME + ''
                  set
                     '' + a.COLUMN_NAME + '' = '''''' + db_name() + ''''''''
         end
                  from
                     INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
                  where
                     UPPER(a.COLUMN_NAME) in
                     (
                        ''COMPANYID'', ''CMPANYID'', ''INTERID'', ''DB_NAME'', ''DBNAME''
                     )
                     and b.INTERID = db_name()
                     and COLUMN_DEFAULT is not null
                     and rtrim(a.TABLE_NAME) + '' - '' + rtrim(a.COLUMN_NAME) <> ''SY00100 - DBNAME''
                  order by
                     a.TABLE_NAME
                  set
                     nocount
                     on OPEN G_cursor FETCH NEXT
                  FROM
                     G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> - 1)
                     begin
                        exec (@cStatement) FETCH NEXT
                  FROM
                     G_cursor INTO @cStatement
                     end
                     close G_cursor DEALLOCATE G_cursor
                  set
                     nocount off'

                  from
                     SY00100 exec (@Statement)
end
else
   begin
      declare @cStatement varchar(255)
      declare G_cursor CURSOR for
      select
         case
            when
               UPPER(a.COLUMN_NAME) in
               (
                  'COMPANYID',
                  'CMPANYID'
               )
            then
               'update ' + a.TABLE_NAME + ' set ' + a.COLUMN_NAME + ' = ' + cast(b.CMPANYID as char(3))
            else
               'update ' + a.TABLE_NAME + ' set ' + a.COLUMN_NAME + ' = ''' + db_name() + ''''
         end
      from
         INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
      where
         UPPER(a.COLUMN_NAME) in
         (
            'COMPANYID', 'CMPANYID', 'INTERID', 'DB_NAME', 'DBNAME'
         )
         and b.INTERID = db_name()
         and COLUMN_DEFAULT is not null
      order by
         a.TABLE_NAME
      set
         nocount
         on OPEN G_cursor FETCH NEXT
      FROM
         G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> - 1)
         begin
            exec (@cStatement) FETCH NEXT
      FROM
         G_cursor INTO @cStatement
         end
         close G_cursor DEALLOCATE G_cursor
      set
         nocount off
   end

How to run the script:

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. Click “New Query”.
3. In the dropdown box next to the “Execute” button, select your new test company database (e.g., TWOTEST). This is essential; running this script against the wrong database can cause significant problems.
4. Paste the entire script into the query window.
5. Click “Execute”.

The script iterates through tables in the test database, finds columns named COMPANYID, CMPANYID, INTERID, DB_NAME, or DBNAME, and updates their values to reflect the details of the new test database as registered in the DYNAMICS database (SY01500 table). The DB_NAME and DBNAME columns are updated to the test database name (e.g., TWOTEST), while COMPANYID and CMPANYID are updated to the internal company ID assigned by GP Utilities.

Explanation of the Script:

The script has two main branches, checking for the existence of the SY00100 table, which is relevant for older versions of GP. Both branches achieve the same goal using a cursor: dynamically building and executing UPDATE statements for relevant columns across tables in the test database.

• It queries INFORMATION_SCHEMA.COLUMNS to find columns with specific names (COMPANYID, CMPANYID, INTERID, DB_NAME, DBNAME).
• It joins this information with the SY01500 table in the DYNAMICS database to find the CMPANYID and INTERID (database name) that corresponds to the test database’s name (db_name()).
• Based on the column name found, it constructs an UPDATE statement:
  • If the column is COMPANYID or CMPANYID, it updates the value to the new CMPANYID from the SY01500 table.
  • If the column is INTERID, DB_NAME, or DBNAME, it updates the value to the new database name (db_name()).
• The script then executes these dynamically generated UPDATE statements using a cursor (EXEC (@cStatement)), ensuring every affected column in every affected table is updated.

This script is crucial for maintaining data integrity and ensuring Dynamics GP functions correctly when accessing data in the test company, as internal references rely on these identifiers being accurate.

Step 5: Verify Data and Configuration¶

After running the script, the technical process of creating the test company is complete. However, it’s essential to perform functional verification and make necessary configuration adjustments in the test environment.

1. Launch Dynamics GP and log in to the new test company.
2. Verify Data: Navigate to key areas and check data consistency. For example, check customer balances, vendor lists, item quantities, account balances, and recent transactions. Compare a few records against the live system to ensure the data looks correct and was restored properly.
3. Disable Integrations: If your live company has integrations with other systems (e.g., CRM, warehousing, e-commerce), these integrations might still be configured in the test company data. It is absolutely critical to disable or reconfigure any integrations that could potentially send test data or transactions to live external systems. Look for integration settings, scheduling, or connection managers within Dynamics GP or external integration tools.
4. Change Email and Printer Settings: Review company setup, user setup, and report options for email addresses and printer destinations. Update these settings to prevent test documents (like invoices, purchase orders, or reports) from being accidentally emailed to real customers or printed to live production printers. Consider changing default report destinations to ‘Screen’ or a specific test printer.
5. Notify Users: Inform users who will be using the test company about its purpose and name. Provide clear instructions on how to access it and remind them not to perform any actions intended for the live system.

Taking these verification and configuration steps ensures that the test environment is isolated and safe for experimentation without unintended side effects on your live business operations.

Understanding the SQL Script Details¶

Let’s delve a bit deeper into the provided SQL script, as it’s the most complex part of the process and crucial for the test company’s functionality.

The script is designed to be somewhat backward-compatible by checking for the SY00100 table, which was used in older versions of GP to hold company-specific path information. The primary goal, however, remains the same: updating internal pointers.

The script leverages INFORMATION_SCHEMA.COLUMNS to programmatically find columns in all tables within the current database (which is the test database) that have names relevant to company identification. These names are typically COMPANYID, CMPANYID, INTERID, DB_NAME, and DBNAME.

It then joins this information with the DYNAMICS.dbo.SY01500 table. The SY01500 table is where Dynamics GP stores the list of all registered companies, mapping their INTERID (which is usually the database name) to a unique internal CMPANYID and the user-friendly Company Name.

The crucial part of the WHERE clause is b.INTERID = db_name(). This line tells the script to find the entry in the DYNAMICS.dbo.SY01500 table that corresponds to the current database name (i.e., the name of your test database, obtained using the db_name() function). This allows the script to retrieve the correct new CMPANYID that GP Utilities assigned to your test company.

A cursor is used because you cannot directly update columns based on data retrieved from INFORMATION_SCHEMA.COLUMNS in a single UPDATE statement across multiple tables. The cursor iterates through each identified column in each identified table that needs updating. For each such column, it dynamically constructs a SQL UPDATE statement as a string variable (@cStatement).

For example, if the script finds a column named COMPANYID in the RM00101 table (Customer Master), and the new CMPANYID for your test database is 3, the cursor will generate a statement like:

update RM00101 set COMPANYID = 3

If it finds a column named INTERID in the GL10000 table (General Ledger Transactions), it will generate a statement like:

update GL10000 set INTERID = 'TWOTEST'

The EXEC (@cStatement) command then executes this dynamically built update statement. This process repeats for every relevant column found across all tables in the test database.

The SET NOCOUNT ON and the cursor structure (OPEN, FETCH NEXT, WHILE @@FETCH_STATUS <> -1, EXEC, CLOSE, DEALLOCATE) are standard SQL patterns for iterating and executing dynamic commands.

The exclusion rtrim(a.TABLE_NAME) + '' - '' + rtrim(a.COLUMN_NAME) <> ''SY00100 - DBNAME'' in the first part of the script (for systems with SY00100) is a specific exclusion to prevent it from trying to update the DBNAME column within the SY00100 table itself using the general rule, which might not be the intended behavior for that specific legacy table.

In essence, the script is a sophisticated way to search and replace the old live company’s identifiers with the new test company’s identifiers throughout the entire database programmatically.

Post-Creation Best Practices¶

Once your test company is up and running, consider these best practices to manage it effectively:

• Document the Process: Keep a record of the steps you followed, including the database name, logical company name, and any specific configuration changes you made (like disabling integrations). This documentation is invaluable for repeating the process later or for troubleshooting.
• Control Access: Limit access to the test company to only those users who need it for testing or training purposes. This minimizes the chance of confusion or accidental data entry by users unfamiliar with the test environment.
• Periodically Refresh Data: Test data can become stale over time, losing its resemblance to the current live data. Depending on your needs, plan to periodically refresh the test company by repeating Steps 1 and 2 (backup live, restore over test database) and then re-running Step 4 (the SQL script) and Step 5 (verification and configuration). The frequency of refreshing depends on how often your live data changes significantly and how current the test data needs to be for your testing purposes.
• Manage Disk Space: Test company databases consume significant disk space. Regularly review the size of your test databases. If you have multiple test companies or frequently refresh them, disk space can become a limiting factor.
• Use Snapshots (if applicable): If your SQL Server environment is virtualized, consider using VM snapshots of the test SQL Server after the creation process is complete and verified. This can provide a quick way to revert the test environment to a known good state before starting a new round of testing that might involve potentially disruptive changes.

By following these practices, you can ensure your test environment remains a valuable and reliable resource for your Dynamics GP system.

Troubleshooting Common Issues¶

While the process is generally straightforward, you might encounter issues. Here are a few common ones:

• Script Permissions Error: The SQL script requires permissions to query system views (INFORMATION_SCHEMA.COLUMNS) and update tables within the target database. Ensure the user executing the script has sufficient permissions (e.g., db_owner on the test database).
• Script Syntax Error: Ensure you copy the script exactly and paste it correctly into SSMS. Sometimes, formatting issues from copying can cause errors.
• Running Script on the Wrong Database: This is a critical error. Always double-check that you have selected the test company database in the SSMS query window dropdown before executing the script. Running it on the live or DYNAMICS database will cause severe problems.
• Test Company Not Appearing in GP Login: If you completed Step 3 in GP Utilities successfully, but the company doesn’t appear in the company selection list when you log into GP, verify that the database name entered in Utilities matches the actual database name on the SQL server exactly. Also, check the DYNAMICS.dbo.SY01500 table directly in SSMS to see if the new company was registered.
• Data Inconsistencies or Errors After Script: If you encounter errors or strange behavior when navigating or processing transactions in the test company, it might indicate the script didn’t run correctly or fully, or that there were issues with the original restore. Re-running the script (carefully selecting the test database) might resolve it. If not, consider deleting the test database, dropping the company in Utilities, and repeating the entire process.
• Integrations Triggering: If you forgot to disable integrations, you might notice test data appearing in external systems. Immediately disable the integration points in the test environment and clean up any unwanted data in the external system.

Careful execution of each step and thorough verification can prevent most issues.

Visualizing the Process Flow¶

Here is a simple flow chart illustrating the steps involved in creating the test company:

mermaid graph TD A[Start Process] --> B{Backup Live DB}; B --> C{Restore Backup to New DB}; C --> D{Create Company Entry in GP Utilities}; D --> E{Run SQL Script to Update IDs}; E --> F{Verify Data & Configure Test Env}; F --> G[End Process];

This diagram visually represents the sequential nature of the tasks required.

Supporting Media¶

Here is a relevant video that demonstrates some of the core concepts involved, such as backing up and restoring SQL databases for use with Dynamics GP. While it may not cover the exact script provided, the database management steps are fundamental to the process.

(Note: Finding a single, high-quality, recent YouTube video covering exactly this full process including the specific script is challenging. The following is a general video about GP database maintenance that covers backup/restore, which is 80% of the process).

<iframe width="560" height="315" src="https://www.youtube.com/embed/YOUR_YOUTUBE_VIDEO_ID" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-fullscreen" allowfullscreen></iframe>

(Replace YOUR_YOUTUBE_VIDEO_ID with an actual relevant video ID found during the search, e.g., one demonstrating GP database backup/restore via SSMS or GP Utilities company setup. If no single video covers enough, this placeholder indicates where a relevant resource would be placed.)

Alternatively, a screenshot showing SQL Server Management Studio with the query window open, pointing to the test database and the script ready to run, could be a helpful visual aid.

Step	Action	Tool Used	Target Database	Key Outcome
1	Back up Live Company Database	SQL Server Management Studio (SSMS)	Live Database	.bak backup file created
2	Restore Backup to New Database	SQL Server Management Studio (SSMS)	New Test Database Name	Test Database created on SQL Server with live data
3	Create Company Entry	Dynamics GP Utilities	DYNAMICS Database	New Company registered in GP, assigned CMPANYID
4	Run SQL Script	SQL Server Management Studio (SSMS)	New Test Database Name	COMPANYID, INTERID, etc. updated within test database tables
5	Verify & Configure	Dynamics GP Client	Test Company	Test environment ready for use, isolated from live

This table provides a quick summary of each major step, the tool used, the database acted upon, and the result.

Creating a test company with live data is a standard and necessary procedure for effective Dynamics GP management and testing. While it involves several steps across different tools, understanding the purpose of each step, particularly the critical role of the SQL script, ensures a successful outcome. This safe environment empowers you to test changes, train users, and troubleshoot effectively, ultimately contributing to a more stable and reliable production system.

What are your experiences with creating test companies in Dynamics GP? Have you encountered any specific challenges or developed any best practices? Share your thoughts and questions in the comments below!