SUMMARY
To test certain issues, a troubleshooting technique may be to copy the Live Company database to a Test Company database.
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
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:
Article ID : 871973
Last Review : N/A
Revision : 17.1
• 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 will not be copied over. To open this window, click Tools,
point to Setup, point to Human Resources, point to Attendance, and then click Setup. This information is not copied over because the TAST0130 table
contains a reference to the Live Company database. To correct this issue, update the Attendance Setup window in the new Test company database to contain
the same information as the Live database. Or, write an update statement to change the company name reference in the TAST0130 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 one of the following options:
• Microsoft Dynamics GP 9.0:
Click Tools, point to Setup, point to Fixed Assets, and then click Company.
• 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, the tables that you have audits on the Live Company database will result in the audit triggers copied
over to the Test Company database. Therefore we recommend to stop the Auditing in the Test Company database. To do this, click on Microsoft Dynamics
GP in the Test Company, point to Tools, point to Setup, point to Company, point to Audit Trails and click on Audit Trail Maintenance. In the Audited
Tables section, click to select each SQL Table Name and click on Stop Auditing. The colored symbol next to the table will change to a black diamond. Do this
for each table in the list. This will stop the audit tracking so activity from your test company does not update the live Audit database.
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 one of the following options.
• Microsoft Dynamics GP 9.0:
Click Tools, click Setup, click System, and then click User Access.
• 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 one of the following options.
• Microsoft Dynamics GP 9.0: Click Tools, click Setup, click System, and then click User Access.
• 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.
Method 1
If you are using SQL Server Enterprise Manager, follow these steps:
a. Click Start, and then click Programs.
b. Point to Microsoft SQL Server, and then click Enterprise Manager.
c. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand the instance of SQL Server.
d. Expand Databases, right-click the live company database, click All Tasks, and then click Backup Database.
e. In the SQL Server Backup window, click Add in the Destination section.
f. In the Select Backup Destination window, click the ellipsis button next to the File name field.
g. In the Backup Device Location window, expand the folders, and then select the location for the backup file.
h. Type a name for the backup file. For example, type Live.bak.
i. Click OK repeatedly until you return to the SQL Server Backup window.
j. Click OK to start the backup.
k. When the backup has completed successfully, click OK.
Method 2
If you are using SQL Server Management Studio, follow these steps:
a. Click Start, and then click Programs.
b. Point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, 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.
Method 1
If you are using SQL Server Enterprise Manager, follow these steps:
Method 2
If you are using SQL Server Management Studio, follow these steps:
If you are using Microsoft Dynamics GP 10.0, follow these steps to copy the security permissions from the live company to the test company:
a. In Enterprise Manager, right-click the test company database, click All Tasks, and then click Restore Database.
b. In the Restore as database field on the General tab, verify that the test company database is selected.
c. In the Restore section, click to select the From device check box.
d. In the Parameters section, click Select Devices, and then click Add in the Choose Restore Devices window.
e. In the Choose Restore Destination window, click the ellipsis button next to the File name field.
f. In the Backup Device Location window, expand the folders, locate and then click the live company backup file that you created in step 4.
g. Click OK repeatedly until you return to the Restore Database window.
h. Click the Options tab.
i. Click to select the Force restore over existing database check box.
j. Verify that a valid path appears in the Move to physical file name field, and then verify that the .mdf and .ldf file names are for the test company
database that you created in step 1.
Note The logical file name reflects the name of the live database. Do not change the logical file name.
k. Click OK to start restoring the live company database into the test company database.
l. When the restore has completed successfully, click OK.
a. Click Start, and then click Programs.
b. Point to Microsoft SQL Server 2005 or Microsoft SQL Server 2008, 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
a. Log on to Microsoft Dynamics GP 10.0 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 or in Enterprise Manager.
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 DYNAMICS database SY01500 table for this test company.
/******************************************************************************/
/* Description: */
/* Updates any table that contains a company ID or database name value */
/* with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table */
/* */
/******************************************************************************/
if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables
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, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
Exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
select [tableName] as 'Tables that were Updated' from ##updatedTables
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.
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'
The test company should now have a copy of the live company data and be ready for use.