Set up a test company that has a copy of live company data by using Microsoft Dynamics GP on MSDE 2000, SQL Server 2005 Express, SQL Server 2008 Express, or SQL Server 2012 Express

Article translations Article translations
Article ID: 872370 - View products that this article applies to.
Expand all | Collapse all

INTRODUCTION

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.

Notes
  • Microsoft Dynamics GP 9.0 is not supported on SQL Server 2008. Microsoft Dynamics GP 2010 is not supported on SQL Server 2000.
  • If you use Record Level Notes in your existing live company, and if you plan to use them in the test company, you will be required to run a NoteFix. For more information, call Technical Support for Microsoft Dynamics at (888) 477-7877.
  • If you use Human Resources for Microsoft Dynamics GP, the Attendance Setup information will not be copied to the test company. To open this window, do one of the following:
    • In Microsoft Dynamics GP 10.0 or later, click Microsoft Dynamics GP, point to Tools, point to Setup, point to Human Resources, and then click Setup.
    • In Microsoft Dynamics GP 9.0, click Tools, point to Setup, point to Human Resources, point to Attendance, and then click Setup.
    The Attendance Setup 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 that is contained in the live database.
  • If you use Fixed Assets for Microsoft Dynamics GP, the Fixed Assets Company Setup information will not be copied to the test Company. To correct this issue, open the Fixed Assets Company Setup window in the live company, and then note the settings. Open the Fixed Assets Company Setup window in the test company, and then enter the same settings as those that are in the live company. To open the Fixed Assets Company Setup window, use one of the following options:
    • In Microsoft Dynamics GP 10.0 or later, click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, point to Fixed Assets, and then click Company.
    • In Microsoft Dynamics GP 9.0 click Tools, point to Setup, point to Fixed Assets, and then click Company.
  • If you use Audit Trails for Microsoft Dynamics GP, the tables that have audits on the live company database will also have audits in the test company database. To remove Auditing in the test company database, call Technical Support for Microsoft Dynamics at (888) 477-7877.
For more information about how to set up a test company that has a copy of live company data by using Microsoft SQL Server 7.0/2000/2005, click the following article number to view the article in the Microsoft Knowledge Base:
871973 Set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, SQL Server 2005, or SQL Server 2008

More information

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:
  1. 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.
  2. Make a backup of the live company database. To do this, follow these steps:
    1. In Microsoft Dynamics GP 10.0 or later, point to Maintenance on the Microsoft Dynamics GP menu, and then click Backup. In Microsoft Dynamics GP 9.0, click Backup on the File menu.
    2. Click the live company that you want to back up.
    3. Verify the path of the backup location, and then click OK.
  3. Restore the backup that you made in step 2 into the test company database. To do this, follow the appropriate steps.

    Using MSDE 2000
    1. Run the following script in Support Administrator Console.

      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'
      
      Notes
      • Replace the <TEST> placeholder with the name of the test company.
      • In the second line of code, replace the path with the physical path of the backup of the live company.
      • The first MOVE statement specifies the location of the .mdf file. Replace the first .mdf file with the .mdf file of the live production database. In this example, the live production database is TWO. Therefore, the .mdf file is GPSTWODat.mdf.

        Following "TO," replace the path with the physical path of the .mdf file of the test database. In this example, the test database is TEST, and the .mdf file is located in C:\Program Files\Microsoft SQL Server\MSSQL\Data.
      • The second MOVE statement specifies the location of the .ldf file. Replace the first .ldf file with the .ldf file of the live production database. In this example, the live production database is TWO. Therefore, the .ldf file is GPSTWOLog.ldf.

        Following "TO," replace the path with the physical path of the .ldf file of the test database. In this example, the test database is TEST, and the .ldf file is located in C:\Program Files\Microsoft SQL Server\MSSQL\Data.
    Using Microsoft SQL Server 2008 Express or Microsoft SQL Server 2005 Express
    1. Do one of the following:
      • If you use Microsoft SQL Server 2008 Express or Microsoft SQL Server 2012 Express, click Start, point to Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
      • If you use Microsoft SQL Server 2005 Express, click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
    2. In the Server name box, type the name of the instance of SQL Server.
    3. In the Authentication list, click SQL Server Authentication.
    4. In the User name box, type sa.
    5. In the Password box, type the password for the sa user, and then click Connect.
    6. In the Object Explorer area, double-click Databases.
    7. Right-click the test company database, point to Tasks, point to Restore, and then click Database.
    8. In the Source for restore area, click From device, and then click the lookup button.
    9. In the Backup location area, click Add.
    10. Locate the live backup file, click the file, and then click OK two times.
    11. In the Select the backup sets to restore area, click Restore for the backup file that you want to restore.
    12. In the Select a page area, click Options.
    13. In the Restore the database files as area, verify that the names of the .mdf file and the .ldf file in the Restore As column match the names of the .mdf file and .ldf file in the test company.
    14. Click OK.
  4. 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
  5. 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.

References

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
870036 How to install the Support Administrator Console for Small Business Manager
To download SQL Server Management Studio for SQL Express, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en

Properties

Article ID: 872370 - Last Review: March 8, 2013 - Revision: 14.0
Applies to
  • 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
  • Microsoft Business Solutions–Great Plains 7.5
  • System Manager
Keywords: 
kbexpertiseinter kbhowto kbinfo kbexpertisebeginner kbmbsinstallation kbmbsmigrate KB872370

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com