# Dynamics GP Restore Live to Test Company Script Generator (KB 871973)

> Source: https://netray.co/tools/dynamics-gp-restore-live-to-test-company-script-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter live and test database names, backup path and file locations to get the complete refresh script: backup, restore with MOVE, the Microsoft INTERID update, owner fix and post steps.

## Category

microsoft-dynamics

## Erp

Microsoft Dynamics GP

## Audience

- GP administrator
- DBA
- GP consultant

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Live company database

**Key:** live_db

**Type:** text

**Default:** TWO

#### Test company database

**Key:** test_db

**Type:** text

**Default:** TEST

#### System database

**Key:** system_db

**Type:** text

**Default:** DYNAMICS

#### Backup file path

**Key:** backup_path

**Type:** text

**Default:** D:\SQLBackup\TWO_copy.bak

#### Live data file logical name

**Key:** data_file_logical

**Type:** text

**Default:** GPSTWODat.mdf

**Help:** Confirm with RESTORE FILELISTONLY.

#### Live log file logical name

**Key:** log_file_logical

**Type:** text

**Default:** GPSTWOLog.ldf

#### Test data file path (existing test .mdf)

**Key:** test_data_path

**Type:** text

**Default:** D:\SQLData\GPSTESTDat.mdf

#### Test log file path (existing test .ldf)

**Key:** test_log_path

**Type:** text

**Default:** L:\SQLLogs\GPSTESTLog.ldf

**Blocks:**

#### 0. Before you start

**Body:** -- 1. The test company {{test_db}} already exists, created in GP Utilities with its own company ID.
-- 2. All users are out of GP; Management Reporter services are stopped if MR is used.
-- 3. If Analytical Accounting is active in {{live_db}}, activate AA in {{test_db}} before restoring.
-- 4. Run every statement below on the test side only. Never run block 4 or later in {{live_db}}.

#### 1. Back up live (copy-only, does not break the backup chain)

**Body:** BACKUP DATABASE [{{live_db}}]
TO DISK = N'{{backup_path}}'
WITH COPY_ONLY, INIT, COMPRESSION, STATS = 10;
-- remove COMPRESSION if your SQL Server edition does not support it

#### 2. Check logical file names

**Body:** RESTORE FILELISTONLY FROM DISK = N'{{backup_path}}';
-- LogicalName column: use these values in the MOVE clauses below; do not rename them

#### 3. Restore over the test database

**Body:** USE master;
ALTER DATABASE [{{test_db}}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [{{test_db}}]
FROM DISK = N'{{backup_path}}'
WITH MOVE N'{{data_file_logical}}' TO N'{{test_data_path}}',
     MOVE N'{{log_file_logical}}' TO N'{{test_log_path}}',
     REPLACE, STATS = 10;

ALTER DATABASE [{{test_db}}] SET MULTI_USER;
ALTER DATABASE [{{test_db}}] SET RECOVERY SIMPLE;  -- optional for test

#### 4. Update COMPANYID and INTERID (Microsoft KB 871973 logic, run in the test database)

**Body:** USE [{{test_db}}];
GO
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, {{system_db}}.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
GO
-- if a duplicate key error names PKxxxxx, fix INTERID/COMPANYID in table xxxxx manually

#### 5. Human Resources attendance setup (only if HR is used)

**Body:** UPDATE TAST0130 SET COMPANYCODE_I = '{{test_db}}';
-- or re-enter Tools > Setup > Human Resources > Attendance > Setup in the test company and save

#### 6. Database owner

**Body:** USE [{{test_db}}];
EXEC sp_changedbowner 'DYNSA';
-- equivalent on newer SQL Server versions:
-- ALTER AUTHORIZATION ON DATABASE::[{{test_db}}] TO DYNSA;

#### 7. After the restore

**Body:** -- Analytical Accounting: run the AAG00102 next-number fix from the Microsoft-linked AA test company article before entering transactions.
-- Fixed Assets: re-enter Fixed Assets Company Setup in the test company.
-- SSRS / Excel report drilldowns: run GP Database Maintenance, Functions and Stored Procedures plus Views, for all databases.
-- Workflow and email: change approver emails and SMTP settings so test does not mail real users.
-- Integrations, eConnect jobs, scheduled SmartConnect or SQL Agent jobs: point them at {{test_db}} or disable them.
-- Optional: rename the company display name in GP so users can see they are in test.

**Notes:** Block 4 follows the script in Microsoft's 'Set up a test company' article (KB 871973) with the system database name taken from your input; if your GP version's article differs, copy Microsoft's current script. The SY00100-DBNAME exclusion keeps the system database name intact. Confirm logical file names with block 2 before running block 3. Take a fresh backup of the test database first if it holds work you need.

## Content

**Intro:** This generator writes the full T-SQL sequence to refresh a Dynamics GP test company from live: copy-only backup, restore with MOVE over the test database, the Microsoft KB 871973 COMPANYID and INTERID update, the DYNSA owner fix and the post-restore steps. Fill in your database names and paths and run it block by block.

**Sections:**

#### How to use it

**Body:** Create the test company once in GP Utilities and grant users access; after that you only repeat the refresh. Enter the live and test database names, the system database name, a backup path the SQL Server service can write to, and the physical paths of the existing test .mdf and .ldf files (from the test database's Files page). Run block 2 first to read the logical names, correct the fields if needed, then run blocks 1, 3, 4 and 6 in order. Log in to the test company and check a few documents before letting users in.

**Bullets:**

- COPY_ONLY keeps your live backup chain intact
- MOVE paths must point at the test files, not live
- Block 4 must run in the test database
- Keep the logical file names from live

#### Why the INTERID script is needed

**Body:** A company database stores its own company ID and database name in many tables (COMPANYID, CMPANYID, INTERID, DB_NAME, DBNAME columns). After restoring live over test, those columns still say live. GP then reads test data with live identifiers, which shows up as wrong company names, posting errors or errors opening windows. The script reads the correct company ID from SY01500 in the system database for the current database name and updates every such column, except SY00100.DBNAME, which holds the system database name and must not change.

#### Worked example

**Body:** Live TWO, test TEST, system DYNAMICS. Backup to D:\SQLBackup\TWO_copy.bak. RESTORE FILELISTONLY shows logical names GPSTWODat.mdf and GPSTWOLog.ldf. The restore moves them onto D:\SQLData\GPSTESTDat.mdf and L:\SQLLogs\GPSTESTLog.ldf with REPLACE. In TEST, the cursor sets every COMPANYID or CMPANYID column to the TEST company ID from DYNAMICS..SY01500 and every INTERID, DB_NAME or DBNAME column (except SY00100.DBNAME) to 'TEST'. sp_changedbowner sets the owner to DYNSA. Users then log in to TEST and see live data as of the backup.

#### Gotchas

**Body:** Restoring over the wrong database is the real risk: double-check test_db before running block 3, and never point MOVE at live files. Analytical Accounting must already be active in test before the restore, and the AAG00102 next numbers need the separate fix or new transactions hit duplicate keys. Fixed Assets company setup and HR attendance setup (TAST0130) need re-entry. Integrations, workflow emails and scheduled jobs copied in data can act on real customers from test, so disable them. Management Reporter needs its services stopped during the refresh.

**Cta:** If you want an AI assistant that answers GP questions from a safe test or reporting copy on your own servers, we can set that up and keep it refreshed.

**Faq:**

#### How do I copy live data into a Dynamics GP test company?

**Answer:** Create the test company in GP Utilities, back up the live database, restore that backup over the test database with MOVE to the test files and REPLACE, then run Microsoft's INTERID update script in the test database and set the owner to DYNSA. This generator writes each step with your names.

#### What does the KB 871973 script do?

**Answer:** It loops over every column named COMPANYID, CMPANYID, INTERID, DB_NAME or DBNAME in the test database and updates it to the test company's ID and database name taken from SY01500 in the system database. Without it, test keeps live identifiers and GP behaves incorrectly.

#### Do I have to recreate the test company every time?

**Answer:** No. Create it once in GP Utilities so it exists in SY01500 with its own ID and has user access. Each refresh is just backup, restore over the same database, the INTERID script and the post steps. Re-grant security only if you changed users since.

#### What breaks after restoring live to test?

**Answer:** Usually HR attendance setup (TAST0130), Fixed Assets company setup, Analytical Accounting next numbers in AAG00102, SSRS drilldown links and any integration or email that still points at live users. Work through block 7 of the output before letting users test.

## Search terms

dynamics gp restore live to test script, dynamics gp restore prod into test, dynamics gp test company script, dynamics gp restore test from live, dynamics gp test company, dynamics gp test from live, kb 871973 interid script

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json