Hi,
First, you need to use GP Utilities and create a new company. You can use the 'backup and restore method, listed first. Or you can use the Copy Company feature of the PSTL Utilities.
Here's a great post from Jen Bieker on January 6, 2017
PSTL Company Copy vs. Dynamics GP Utility Create a Company
I’m going to admit, I’ve been a bit confused as to why and when I would want to use these two tools. So, I’ve been asking around and I think I’ve finally got it.
- What’s the difference?
- When and why would I use each of them?
Here’s my breakdown.
Dynamics GP Utility – Create a Company
This built-in utility only creates a shell of a company. In other words, no data, no customers, no chart of accounts, no fiscal years, nothing is copied over.
For example, it’s used to create an empty shell of a new Dynamics GP database. You might use it to create a brand new company. Or you can go a step further, and after creating the new company, do a SQL backup and restore of your current system, and last, with the help of a handy SQL script to clean up a few things, you can have a test system to match your current one.
Read more on the GP Utility.
PSTL – Company Copy
This tool does deal with data. You can pick and choose which pieces of data you want and it will move it into a new database.
This is great when you have different environments you need databases for. It’s a starting point for a new database.
Read more on how to use the PSTL Company Copy tool here.
Here's a handy PDF guide with step-by-step instructions on how to use these tools to create a new company in Dynamics GP. Download PDF Here
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Below is a copy of the detailed instructions from Microsoft support:
BACKUP AND RESTORE METHOD
Set up a test company that has a copy of live company data for Microsoft Dynamics GP by using Microsoft SQL Server
SUMMARY
This article describes how to set up a test company that has a copy of live company data by using Microsoft SQL Server.
MORE INFORMATION
Notes •If you use Record Level Notes - in your existing live company and plan to use them in the test company, you must run the NoteFix utility. For more information, contact the Microsoft Business Solutions System Technical Support team by telephone at (888) 477-7877.
•If you are using Human Resources for Microsoft Dynamics GP, the Attendance Setup information appears to have not been copied over. To open this window, click Tools, point to Setup, point to Human Resources, point to Attendance, and then click Setup. This table (TAST0130) is copied over, but it contains a field that still references the Live Company database. To correct this issue, you can simply re-enter the data in the Attendance Setup window in the new Test company database to contain the same information as before and Save it. Or, you may choose to update the COMPANYCODE_I field in the TAST0130 table to change the company code reference to Test database instead (which can be found in the INTERID column value for the Test company in the Dynamics..SY01500 table).
•If you are using Fixed Assets for Microsoft Dynamics GP, the Fixed Assets Company Setup information will not be brought over to the Test Company. To correct this issue, open the Fixed Assets Company Setup window in the Live Company and note the settings. Open the Fixed Assets Company Setup window in the Test Company and enter the same settings as the Live Company. To open the window, use the following: •Microsoft Dynamics GP 10.0 or a later version:
Click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, point to Fixed Assets, and then click Company.
•If you are using Audit Trails for Microsoft Dynamics GP, you must delete the audit triggers from the test company using SQL and not from the front-end. Audit Trails is just triggers that are copied over and still point to the same live audit database. However, do not delete, stop or remove the audit in the Audit Trail Maintenance window in the test company, or this will clear out the history in the audit table and/or remove the trigger on the live company. Refer to steps outlined in the KB article below to remove the audit trail triggers from the test company:
2847491 How to stop Audit Trail triggers in the test company from updating the live audit database using Audit Trails in Microsoft Dynamics GP
•If you are using Analytical Accounting (AA), you must first activate AA in the Test company, before the live company database (that has AA active) can be restored to the Test
company. After the restore is completed, you must then click on the link below to obtain a script to run against the Test company database that will update the next available values stored in the AAG00102 table (to prevent Duplicate Key errors when keying new transactions).
• If you are using Management Reporter 2012, you must stop the Management Reporter services which can be done using either of the following options:
1. In the Management Reporter 2012 Configuration Console, on the first page, you’ll see both the Management Reporter 2012 Application Service and Management Reporter 2012 Process Service. Click Stop under these two services to stop them.
2. Click on Start, click on Control Panel, click on Administrative Tools, then click to open Services. In the Services window, highlight the Management Reporter 2012 Application Service and click on the link to Stop this service. Also, highlight the Management Reporter 2012 Process Service and click the link to Stop this service as well.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
To set up the test company, follow these steps:
1.In Utilities, create a new company database that you can use as the test company. Make sure that you give the database a unique DB/company ID and company name that will designate the database as a test company. For example, you could use a DB/company ID of "TEST" and a company name of "TEST COMPANY."
Note Note the path where the database's .mdf and .ldf files are being created. You will need this information for a step later in this article.
2.Log in to the test company. To do this, use the following step. •Microsoft Dynamics GP 10.0 or later:
Click Microsoft Dynamics GP, click Tools, click Setup, click System, and then click User Access. .
3.In the User Access area, select the user to whom you want to grant access to the test company database. Then, click to select the check box next to the test company name to grant access to the test company database. Repeat this step for all users to whom you want to grant access to the test company database. To do this, use the following step.. •Microsoft Dynamics GP 10.0 and Microsoft Dynamics 2010: Click Microsoft Dynamics GP, click Tools, click Setup, click System, and then click User Access. .
4.Make a backup of the live company database. To do this, use one of the following methods, as appropriate for your situation.
If you are using SQL Server Management Studio, follow these steps:
a.Click Start, and then click Programs.
b.Point to Microsoft SQL Server, and then click SQL Server Management Studio. The Connect to Server window opens.
c.In the Server name box, type the name of the instance of SQL Server.
d.In the Authentication list, click SQL Authentication.
e.In the User name box, type sa.
f.In the Password box, type the password for the sa user, and then click Connect.
g.In the Object Explorer section, expand Databases.
h.Right-click the live company database, point to Tasks, and then click Backup.
i.In the Destination area, click Remove, and then click Add.
j.In the Destination on disk area, click the ellipsis button.
k.Find the location where you want to create the backup file, type a name for the backup file, such as LIVE.bak, and then click OK.
l.Click OK repeatedly until you return to the Backup Database window.
m.Click OK to start the backup.
5.Restore the live company backup file that you created in step 4 into the test company database. To do this, use one of the following methods, as appropriate for your situation.
If you are using SQL Server Management Studio, follow these steps:
a.Click Start, and then clickPrograms.
b.Point to Microsoft SQL Server, and then click SQL Server Management Studio. The Connect to Server window opens.
c.In the Server name box, type the name of the instance of SQL Server.
d.In the Authentication list, click SQL Authentication.
e.In the User name box, type sa.
f.In the Password box, type the password for the sa user, and then click Connect.
g.In the Object Explorer section, expand Databases.
h.Right-click the test company database, point to Tasks, point to Restore, and then click Database.
i.In the Source for Restore area, click From Device, and then click the ellipsis button.
j.In the Backup Location area, click Add.
k.Find the location where saved the backup file, select LIVE.bak file, and then click OK.
l.Click OK. You return to the Restore Database window.
m.In the Select the Backup Sets to Restore section, click the backup file that you want to restore.
n.In the Select a Page area, click Options.
o.In the Restore Database Files as area, you will need to change the location of these two files from the Live database to the test database's .mdf and .ldf files. By default, these will be selected on the Live database's .mdf and .ldf files.
Note The logical file name reflects the name of the live database. Do not change the logical file name.
p.To change these locations, click on the Ellipse (…) next to the file location field.
q.Navigate to the path that you noted in step 1, where the test database was created.
r.Highlight the respective .mdf file, and then click OK.
s.Repeat steps p through r, select the .ldf file, and then click OK.
t.Click to select the Overwrite existing database check box.
u.Click OK to return to the Restore Database window
If you are using Microsoft Dynamics GP 10.0 or later, follow these steps to copy the security permissions from the live company to the test company:
a.Log on to Microsoft Dynamics GP as the sa user.
b.Click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and then click User Access.
c.Select an appropriate user, and then make sure that the check box for the new test company is selected to indicate that access is granted.
Note If you receive an error message when you click to select a company, delete the user from the Users folder under the new test database in SQL Server Management Studio.
d.Click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and then click User Security.
e.In the Security Task Setup window, select the user who you want to have access to the test company.
f.In the Company list, click the live company.
g.Click Copy, click to select the check box that is next to the test company, and then click OK.
The user’s permissions in the live company are copied to the test company.
6.After the live company database has been restored over the top of the test company database, the test company contains references that have the same COMPANYID and INTERID information that the live company has. To correctly reflect the information for the test company, run the following script below against the test company in Query Analyzer or in SQL Server Management Studio. This script updates the COMPANYID and INTERID in the test database with the information that is listed in the system database SY01500 table for this test company.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note If this script fails with a duplicate key error, you must manually change the INTERID and COMPANYID columns in the table on which you are receiving the primary key error in the test company.
For example: A primary key constraint error on "PKRVLPD033." To properly perform a search for the table, the prefix, PK, refers to Primary Key and is not part of the table name. In this example, the table that you want to verify is "RVLPD033" for that database.
Note If you are using Human Resources, you must also change the COMPANYCODE_I value in the TAST0130 table. See the NOTES section at the top of this article for more information.
7.Verify that the database owner of the test database is DYNSA. To do this, run the following script against the test company in Query Analyzer or in SQL Server Management Studio:
sp_changedbowner 'DYNSA'
8.If you use the drilldown functionality in the SQL Server Reporting Services or Excel integrated reports you need to do the following to update your server links so the drilldowns work after the database change:
•Ensure that everyone has logged out of Microsoft Dynamics GP and close all instances of SQL Server Management Studio
•On a machine where Dynamics GP is installed click on Start, then point to All Programs. Click on Microsoft Dynamics, then GP 2010 and click on Database Maintenance
•When the utility opens select or enter the SQL Server instance where the Dynamics GP databases are stored. If you are logged in as a domain account with rights to this SQL Server instance you can select that option. Otherwise select SQL Authentication and enter a n appropriate user name and password. Then click Next >>
•Select Mark All to choose each of the Dynamics GP databases and click Next >>
•Select the Microsoft Dynamics GP product, then click Next >>
•Select 'Functions and Stored Procedures' and 'Views', then click Next >>
•Review the confirmation window, then click Next >> to begin the process.
The test company should now have a copy of the live company data and be ready for use.
REFERENCES
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
872370 How to create a test company that has a copy of live company data by using Microsoft Dynamics GP on MSDE 2000 or on SQL Server 2005 Express
Properties
Article ID: 871973 - Last Review: Jun 16, 2017 - Revision: 3
Applies to
Microsoft Dynamics GP 2015, Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2010, Microsoft Dynamics GP 10.0, Microsoft Dynamics GP 9.0, Microsoft Business Solutions–Great Plains 8.0, System Manager, Microsoft Dynamics GP 2015 R2, Microsoft Dynamics GP 2016
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Kind regards,
Leslie
Share
Report
All responses (
Answers (
65,271
