This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
To test certain issues, you may want to use a troubleshooting technique by using your live company data. To do this, you can copy a live company database to a test company database.
This article describes how to create the test company that has a copy of live company data. To do this, you can use Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains running on SQL Server Desktop Engine (also known as MSDE 2000), on Microsoft SQL Server 2005 Express, or on Microsoft SQL Server 2008 Express.
Applies to: Microsoft Dynamics GP
Original KB number: 872370
Note
To create a test company that has a copy of live company data by using Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains running on MSDE 2000, on Microsoft SQL Server 2005 Express, on Microsoft SQL Server 2008 Express or on Microsoft SQL Server 2012 Express, follow these steps:
Create a company database in Utilities that you will use as the test company. Make sure that you give the database a name that will identify the database as a test company. For example, if the live company database is The World Online, Inc. (TWO), the test company database can be named TWOT or TEST.
After the test company is created, you can use the test company without making any modifications to it. Continue with the remaining steps to load the test company with data from the live company.
Make a backup of the live company database. To do this, follow these steps:
Restore the backup that you made in step 2 into the test company database. To do this, follow the appropriate steps.
Using MSDE 2000
Note
Do not select the TEST database in the drop-down list in Support Administrator Console.
RESTORE DATABASE [<TEST>]
FROM DISK = N'C:\Program Files\Dynamics\Backup\TWO_Mar5-2003.bak'
WITH FILE = 1, NOUNLOAD,
STATS = 10, RECOVERY, REPLACE,
MOVE N'GPSTWODat.mdf' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTDat.mdf',
MOVE N'GPSTWOLog.ldf' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTESTLog.ldf'
Note
Using Microsoft SQL Server 2008 Express or Microsoft SQL Server 2005 Express
After you complete the restore process, the COMPANYID information and the INTERID information in the test company match the COMPANYID information and the INTERID information in the live company. This information must be updated to reflect the COMPANYID information and the INTERID information correctly in the test company. Run the following script in Support Administrator Console or in SQL Server Management Studio against the test company to update the COMPANYID information and the INTERID information.
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
Verify that the owner of the TEST database is DYNSA by executing the following script against the TEST database in Support Administrator Console.
sp_changedbowner 'DYNSA'
After you complete these steps, the test company has a copy of the live company data. Therefore, the test company is ready for use in Microsoft Dynamics GP.
To download SQL Server Management Studio for SQL Express, see Download SQL Server Management Studio (SSMS).
Training
Module
Create new companies in Business Central - Training
Learn how to create and set up new companies efficiently in Business Central.
Certification
Microsoft Certified: Dynamics 365 Business Central Developer Associate - Certifications
Demonstrate you have the skills to design, develop, test, and maintain solutions based on Dynamics 365 Business Central.
Documentation
Set up a test company - Dynamics GP
Describes how to set up a test company that has a copy of your live production company data by using Microsoft SQL Server.
How to remove a company that is no longer being used - Dynamics GP
Describes how to remove a company that is no longer being used in Microsoft Dynamics GP.
Delete a company in Microsoft Dynamics GP - Dynamics GP
Describes how to delete a company in Microsoft Dynamics GP if you also run Microsoft SQL Server. The procedure requires that you delete the company both from the DYNAMICS database and from the computer that is running SQL Server.