Dynamics GP: Creating Test & Historical Companies in Payroll (US & Canadian)

Updated: 12 May, 2025 • 0 • min read

Table of Contents

Maintaining a stable and reliable Dynamics GP environment is crucial for any business, especially when managing critical functions like Payroll. The Payroll module, serving both US and Canadian operations, handles sensitive employee data, complex tax calculations, and intricate year-end reporting processes. Making changes or testing new configurations directly in the live production company carries significant risks. This is where the ability to create test and historical companies becomes invaluable. These separate instances of your company data provide safe environments for testing, training, troubleshooting, and accessing past information without jeopardizing your current payroll processing.

Test companies offer a dynamic sandbox reflecting your current data and setup, perfect for simulating processes before executing them live. Historical companies serve as static archives of past periods, primarily used for lookup and reporting purposes after key events like year-end closing. Understanding the distinction and the proper procedure for creating these companies is fundamental to effective Dynamics GP administration for Payroll users in both the United States and Canada. This guide outlines the steps and considerations necessary to successfully create and utilize test and historical companies within the Dynamics GP framework, focusing on the unique aspects and importance for the Payroll module. Utilizing these environments effectively ensures operational continuity and data integrity, especially during critical times like year-end processing or tax updates.

Understanding the Purpose: Test vs. Historical Companies¶

The decision to create a test or a historical company depends entirely on the intended use case. While the underlying technical process involves duplicating a company database, their purpose and typical usage differ significantly.

Test Companies¶

Test companies are typically created as a copy of the current production database. Their primary function is to provide a safe environment where users can perform activities they wouldn’t risk doing in the live company. Common use cases for a test company include:

Testing Tax Updates: Before applying crucial US or Canadian payroll tax updates to the live system, they should always be tested in a test environment. This ensures the update installs correctly and calculates taxes as expected against your specific data.
Training New Users: Training new payroll administrators or processors on a test company prevents accidental data entry or processing errors in the live environment. Users can practice payroll runs, void checks, or make adjustments without consequence.
Troubleshooting Issues: If a complex issue arises in the live system (e.g., a posting problem, an incorrect calculation), replicating the scenario in a test company allows administrators to troubleshoot and find a solution without disrupting live operations.
Previewing Year-End Procedures: The payroll year-end closing process is critical and irreversible. Performing a mock year-end close in a test company using a copy of the data just before the live closing allows you to identify potential issues and verify the results before committing in production.
Testing New Configurations or Add-ons: Implementing new pay codes, deduction codes, benefits, or third-party add-ons can be tested thoroughly in a test company to ensure they function correctly and integrate seamlessly with existing payroll data and processes.

A test company should ideally be refreshed periodically with a new copy of the live database to ensure the data and setup remain current and relevant for testing.

Historical Companies¶

Historical companies, in contrast, are static copies of a company database taken at a specific point in time, usually after a significant closing process like year-end. Their main purpose is archival and read-only access to past data. Common use cases for a historical company include:

Accessing Past Payroll Data: Need to look up details from a payroll run two years ago? Or perhaps investigate employee wage history from a prior fiscal year? A historical company allows you to log in and access this data without cluttering or impacting your current live database.
Generating Prior Year Reports: While some historical reports can be run from the live system, accessing a dedicated historical company ensures the data snapshot is accurate for that specific period and can sometimes be faster or more reliable for complex historical reporting needs.
Auditing and Compliance: Auditors may require access to payroll data from specific past periods. Providing access to a historical company allows them to review the data without needing access to the current live system or potentially interfering with live processes.
Reviewing Past Configurations: Need to see how a specific payroll setup (like a deduction or benefit calculation) was configured in a prior year? The historical company preserves the configuration data as it existed at that time.

Historical companies are typically created once a year after completing the payroll year-end closing procedures for both US and Canadian payrolls. They are generally static and not updated, serving as a permanent record.

Prerequisites and Planning¶

Before embarking on the process of creating a test or historical company in Dynamics GP, some key prerequisites must be met and planning steps undertaken. Proper preparation helps ensure the process is smooth and successful.

SQL Server Access and Knowledge: The creation process involves working directly with the SQL Server database engine that houses your Dynamics GP data. You will need appropriate permissions to back up and restore databases using SQL Server Management Studio (SSMS). Familiarity with basic SQL backup and restore operations is essential.
Server Resources: Duplicating a company database requires significant disk space on your SQL Server. Ensure you have enough free space to accommodate a full copy of your live company database. Additionally, test/historical companies will consume server memory and processing power when users are logged into them, so consider the impact on overall system performance if multiple companies or users are active.
Dynamics GP Utilities Access: The process requires running Dynamics GP Utilities against the restored database. You need administrative access to launch and use GP Utilities.
Communication Plan: Inform users (especially payroll staff) that you will be creating a new company database. Explain the purpose (test or historical) and how they can access it, if applicable. For historical companies, clarify that the data is read-only.
Naming Convention: Decide on a clear and consistent naming convention for your test and historical databases and company names within GP. This helps users easily identify which company they are logging into (e.g., TWO_TEST, TWO_HIST2023, PROD_TEST, PROD_HIST2024).

Dynamics GP Payroll Test Historical Companies

Step-by-Step Guide: Creating a Test or Historical Company¶

The core process for creating both test and historical companies is largely the same, involving database duplication and integration with Dynamics GP. The difference lies primarily in when you take the database backup (current vs. specific past point) and the purpose you assign to the new company.

Step 1: Backup the Live Company Database¶

The first critical step is to create a full backup of your live Dynamics GP company database that you wish to duplicate. This is performed using SQL Server Management Studio (SSMS).

Open SQL Server Management Studio and connect to the SQL Server instance hosting your Dynamics GP databases.
Expand the “Databases” node in the Object Explorer.
Right-click on the live company database you want to copy (e.g., TWO for the Fabrikam sample company, or your actual production company database name).
Navigate to Tasks > Back Up…
Ensure the Backup Type is set to “Full”.
Verify or set the backup destination (usually to a disk). Choose a location with sufficient space and note the filename and path.
Click “OK” to start the backup process. Wait for the process to complete successfully.

A successful backup is the foundation. Ensure the backup file is accessible for the restore operation. It’s often good practice to back up the Dynamics (or DYNAMICS) system database as well before proceeding, although it’s not strictly necessary for creating one company copy, it’s crucial before significant system-wide changes.

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

Next, you will restore the backup you just created, but crucially, you will restore it with a new database name. This creates a duplicate database instance separate from your live one.

In SSMS, right-click on the “Databases” node (not a specific database).
Select “Restore Database…”.
In the “Source” section, select “Device” and click the browse button (…).
Click “Add” and navigate to the backup file (.bak) you created in Step 1. Select the file and click “OK”.
Click “OK” again in the “Select backup devices” window.
In the “Destination” section, the “Database” field will initially show the original database name. Change this name to the desired name for your new test or historical company database (e.g., TWO_TEST, TWO_HIST2023). This is essential to avoid overwriting your live database.
Go to the “Files” page in the left-hand pane. You may need to change the “Restore As” filenames for the data (.mdf) and log (.ldf) files to match your new database name and ensure they are not overwriting existing files. It’s often safest to restore them to a new folder or with filenames that include the new database name (e.g., TWO_TEST.mdf, TWO_TEST_log.ldf).
Go to the “Options” page in the left-hand pane.
Under “Restoring options”, ensure “Overwrite the existing database (WITH REPLACE)” is UNCHECKED unless you are intentionally refreshing an existing test company. For a new test or historical company, this should never be checked.
Under “Recovery state”, usually “RESTORE WITH RECOVERY” is appropriate.
Click “OK” to begin the restore process. Wait for it to complete. A successful restore will create a new database with the name you specified.

Step 3: Update Company ID in the Dynamics Database¶

Dynamics GP needs to know that this new database exists and is available as a company. This is done by adding an entry for the new database in the SY01500 table within the Dynamics system database. This step requires executing a SQL script.

Open a “New Query” window in SSMS, connected to your SQL Server instance.
Ensure the query is targeting the Dynamics database (select Dynamics from the database dropdown or use USE Dynamics;).

Execute the following SQL script. You will need to replace the placeholder values (YourNewDBName, YourNewCompanyName, YourNewCompanyShortName, XXXX) with the actual values for your new company.

USE DYNAMICS;
GO

-- Find the next available company ID
DECLARE @NewCMPANYID INT;
SELECT @NewCMPANYID = ISNULL(MAX(CMPANYID), 0) + 1 FROM SY01500;

-- Insert the new company record
INSERT INTO SY01500 (
    CMPANYID,
    CMPNYNAM,
    CMPNYADD,
    CMPNYCTZ,
    CMPNYSTT,
    CMPNYZIP,
    CMPNYCNT,
    CMPNYPSN,
    CMPNYFAX,
    PHNUMBR1,
    PHNUMBR2,
    PHNUMBR3,
    STAXCERT,
    FEDTAXID,
    STATEID,
    FRANCHID,
    CUSTCLAS,
    VNDRCLAS,
    CHEKBKID,
    NOTEINDX,
    ADDRESS1,
    ADDRESS2,
    ADDRESS3,
    CITY,
    STATE,
    ZIPCODE,
    COUNTRY,
    PHONE1,
    PHONE2,
    PHONE3,
    FAX,
    CCode,
    ADRSCODE,
    Created_Date,
    Created_Time,
    Changed_Date,
    Changed_Time,
    GPServer,
    SqlDataSource,
    SQLInstName,
    DB_NAME,
    Intercompany_ID,
    MC_Company_ID,
    MC_Company_Name,
    DEX_ROW_ID
)
SELECT
    @NewCMPANYID, -- New Company ID
    'YourNewCompanyName', -- The full name of the company in GP
    CMPNYADD,
    CMPNYCTZ,
    CMPNYSTT,
    CMPNYZIP,
    CMPNYCNT,
    CMPNYPSN,
    CMPNYFAX,
    PHNUMBR1,
    PHNUMBR2,
    PHNUMBR3,
    STAXCERT,
    FEDTAXID,
    STATEID,
    FRANCHID,
    CUSTCLAS,
    VNDRCLAS,
    CHEKBKID,
    NOTEINDX,
    ADDRESS1,
    ADDRESS2,
    ADDRESS3,
    CITY,
    STATE,
    ZIPCODE,
    COUNTRY,
    PHONE1,
    PHONE2,
    PHONE3,
    FAX,
    CCode,
    ADRSCODE,
    GETDATE(), -- Created_Date
    GETDATE(), -- Created_Time (Use Time functions if available, GETDATE() works)
    GETDATE(), -- Changed_Date
    GETDATE(), -- Changed_Time (Use Time functions if available, GETDATE() works)
    GPServer, -- Name of the server hosting GP (often 'Dynamics GP')
    SqlDataSource, -- SQL Server Data Source name (e.g., YOURSERVERNAME\YOURINSTANCE)
    SQLInstName, -- SQL Server Instance Name (e.g., YOURSERVERNAME\YOURINSTANCE)
    'YourNewDBName', -- The actual name of the database you restored
    '', -- Intercompany_ID (usually empty)
    0, -- MC_Company_ID (usually 0 unless using multicurrency for companies)
    '', -- MC_Company_Name (usually empty)
    (SELECT ISNULL(MAX(DEX_ROW_ID), 0) FROM SY01500) + 1 -- DEX_ROW_ID

FROM SY01500
WHERE CMPANYID = (SELECT CMPANYID FROM SY01500 WHERE DB_NAME = 'YourOriginalLiveDBName'); -- Replace with your actual live database name

GO

'YourNewDBName': Replace this with the exact database name you used during the restore (e.g., TWO_TEST, TWO_HIST2023).
'YourNewCompanyName': Replace this with the name you want to appear in the GP company login list (e.g., Fabrikam, Inc. (TEST), Fabrikam, Inc. (HIST 2023)).
'YourOriginalLiveDBName': Replace this with the exact database name of your live company that you backed up (e.g., TWO).
The script automatically generates a new CMPANYID and DEX_ROW_ID. It copies other company settings from the original company record, which you can edit later within GP if needed.

Execute the query. Verify that “1 row(s) affected” is displayed in the messages tab.

Step 4: Run Dynamics GP Utilities¶

After restoring the database and adding its reference to the Dynamics database, you need to run Dynamics GP Utilities against the new company database. This step ensures the database is properly integrated with the GP system files and applies any necessary updates or synchronizations.

Close all instances of Dynamics GP and any related applications for all users.
Launch Dynamics GP Utilities (usually found in the Microsoft Dynamics GP program group).
Log in using your sa user credentials or another user with sufficient permissions.
Utilities will scan the databases. If it detects the new database name from Step 3, it should prompt you to verify the database or indicate it needs an update. Follow the prompts.
Select the new company from the list when prompted.
Utilities will go through a process which may involve verifying the database version and potentially applying minor updates if the Utilities version is newer than the database version. Allow this process to complete.
Once Utilities finishes, you will typically exit the application.

Step 5: Log In to Dynamics GP and Configure the New Company¶

The new company should now be available in the Dynamics GP login window.

Launch Dynamics GP.
In the company selection dropdown, you should see the YourNewCompanyName you specified in the SQL script (Step 3).
Select the new company and log in. You may need to log in as sa initially to configure user access.
Verify that the data appears as expected – employee lists, pay codes, past pay runs (if historical), etc.
Crucially, especially for test companies: Review security settings. By default, all users who had access to the original company might have access to the new copy. You may want to limit access in test/historical companies for security or to prevent users from accidentally working in the wrong company. Go to Microsoft Dynamics GP > Tools > Setup > System > User Security to adjust.
For a test company: Perform a small test payroll run using a sample employee to ensure calculations are working correctly. Verify setup information under Human Resources and Payroll setup windows.
For a historical company: Verify you can access historical inquiry windows and reports.

Step 6: Specific Payroll Considerations Post-Creation (US & Canadian)¶

Once the test or historical company is live in GP, there are specific Payroll aspects to consider for both US and Canadian versions.

Data Volume: Payroll databases, especially historical ones spanning multiple years, can be very large. Performance in the test/historical company might be slower than live if server resources are constrained or if the database was not properly maintained before the backup.
Year-End Wage Files (US & Canadian): If creating a historical company after year-end close, verify that the year-end wage files (W-2s, T4s, RL-1s) and summaries are accessible and correct within the historical company. This is a primary reason for creating a historical copy at this time.
Tax History: Ensure tax history data is present and accessible. This includes federal, state, and local taxes for US Payroll, and federal and provincial taxes for Canadian Payroll.
Linked Data: Be aware that some data in GP might be linked across companies (though less common for core payroll) or rely on system-wide settings. Most core payroll data resides within the company database itself.
Integrations: If your live system uses integrations (e.g., for time entry, banking), these integrations might be configured to point to the live company database. Be cautious when testing in a test company, as integration activities might inadvertently affect the live system if not properly reconfigured or disabled in the test environment.

Best Practices and Tips¶

To get the most out of your test and historical companies and avoid potential pitfalls, follow these best practices:

Fresh Backups for Test: For effective testing, especially for things like tax updates, use the most recent possible backup of your live company to create the test company. Data and configuration should be as close to live as possible.
Annual Historical Copies: Make it a standard procedure to create a historical company copy immediately after completing your Payroll year-end closing processes each year for both US and Canadian payrolls. Label them clearly by year.
Document Your Process: Keep a step-by-step guide for your specific environment detailing the backup/restore process, the SQL script (with your specific server and database names), and the Utilities steps. This makes repeating the process easier and less error-prone.
Regularly Review Test Companies: Test companies can consume significant disk space. If a test company is no longer needed or is too outdated to be useful, consider decommissioning it (back it up one last time, then delete the database and remove its entry from SY01500).
Clear Identification: Use distinctive names for test and historical companies in the GP login screen (e.g., adding “(TEST)” or “(HIST 2023)” to the name). You can also customize the company color theme within GP (Microsoft Dynamics GP > Tools > Setup > System > Company > Company Color) to provide a visual cue to users about which company they are in.
Control User Access: Strictly manage user access to test and historical companies, granting access only to those who need it.
Avoid Live System Impact: Ensure any testing activities in a test company (especially those involving external connections or integrations) are configured so they cannot accidentally interact with or modify external systems or the live GP company.

Common Issues and Troubleshooting¶

While the process is generally reliable, you might encounter issues:

SQL Restore Failures: Check the SQL Server error logs for details. Common causes include insufficient disk space, incorrect file paths or filenames for the .mdf and .ldf files during restore, or issues with the backup file itself.
GP Utilities Errors: Errors during the Utilities process often relate to permissions, connectivity issues to the SQL database, or problems with the GP system files. Ensure Utilities is run with sufficient privileges and can connect to the new database.
Performance Issues: Slow performance in test/historical companies, especially large ones, can be due to limited server resources, database fragmentation, or outdated SQL statistics. Regular database maintenance on the test/historical databases can help.
Company Not Appearing in GP: If the company doesn’t show up in the login window after running Utilities, double-check that the SQL script to update SY01500 was executed correctly and that the DB_NAME field in the script exactly matches the name you used when restoring the database.
Security Configuration: Ensure you configure security within the new company after logging in. Users who had access to the original company might automatically have access to the copy until security is modified.

The Value Proposition for Payroll¶

For Payroll departments using Dynamics GP in the US and Canada, the ability to create and manage test and historical company environments is not just a technical option – it’s a critical operational necessity. These environments provide a safety net for testing complex and high-stakes processes, like applying tax updates or performing year-end closes, which directly impact employee payments and regulatory compliance. They empower administrators to experiment, train staff, and troubleshoot without the fear of corrupting live data or halting critical payroll runs. Furthermore, historical companies ensure that valuable past payroll data remains accessible for reporting, auditing, and historical lookups, providing continuity and compliance over time. Implementing and utilizing these practices effectively significantly enhances the reliability, security, and efficiency of your Dynamics GP Payroll operations.

Do you currently use test or historical companies for your Dynamics GP Payroll? What are your biggest challenges or successes with this process? Share your experiences and questions below!

Computacionvinchi