Getting a fresh copy of the production DB applied to Test

  • Getting a fresh copy of the production DB applied to Test

    Posted by DSC Communities on December 16, 2016 at 12:37 pm
    • Rod Carlson


      December 16, 2016 at 12:37 PM

      I have done this a lot with other SQL server databases but want to know if there is anything to watch out for, specifically regarding the GP databases.

      We are thinking of backing up and then copying the “production” database (one particular company) over the top of the “test” database (another company).

      This would allow a fresh test and training environment for our users.

      Are there any concerns with GP databases? Do you have any “lessons learned” that I should be aware of?


      Rod Carlson
      Information Technology – Software Developer
      YMCA of Metro Chicago
      Chicago IL

    • Steve Burney


      December 16, 2016 at 2:17 PM

      Hello Rod.

      Other than your basic tasks for this type of procedure the only thing I can think of is to make sure that your Company Setup in GP has the correct Company ID set to it.


      Steve Burney
      Senior Financial Systems Administrator
      Correct Care Solutions
      Nashville, TN United States
      Twitter @SteveBurney

    • Corey Clay


      December 16, 2016 at 2:21 PM

      If you follow this KB article, you’ll get it right. The only other thing to watch out for is if you or any other user/process have any connections open to the test database, you will need to make sure to stop those connections.

      Corey Clay
      Senior Technical Consultant
      NexTec Group
      Louisville OH

    • Jen Kuntz


      December 18, 2016 at 10:47 AM

      In addition to the other suggestions from others, here are a couple of gotchas to watch for that I’ve run across in my experiences.

      1. Pathnames to be updated

      • For products like EFT, Safepay and other things like that, there are pathnames configured, even posting settings if you print posting journals to file there is a pathname there too. It’s wise to determine if any products or modules you use have paths identified in GP and find where the tables are and have a post-refresh script update the paths as necessary for a test company.

      2. Note Indexes

      • This is pretty trivial but the SY01500 table stores the “next index” for notes. When you refresh a test company, that doesn’t magically get updated and the note indexes from production will likely be much higher a value than a test company. I’ve seen instances where you either get errors because GP tries to use the next number but it already exists or you have notes in places that don’t make sense because the index numbering is from the production company. Perhaps in a post-refresh script, update the setting on the test company db ID record to the same next index as production, since that’s effectively what has occurred by copying it over.

      3. Triggers or SQL objects hard coded to a database

      • I’ve seen some “not ideal” customizations, stored procs, triggers and other things that have been written to hard code them to specific company database IDs. The problem is when you refresh a test company, the code may still fire against a production company and result in things that don’t make sense, or possibly cause issues in various ways.
      • Example: Paramount Workplace has lots of triggers in the GP database and they are hard coded to point to the production database. That’s the only obvious real example I have off the top of my head.

      Hope that helps.


      Jen Kuntz, CPA, CGA
      Microsoft MVP, Business Solutions
      Kuntz Consulting Inc.
      Cambridge, ON, Canada

    • T W


      December 19, 2016 at 3:54 AM

      I have a restore of our production live company to a test company every Friday night, automated, comes in handy in encouraging users to use test company by keeping the data fresh.

      Schedule restore of live company to test company Dyanamics GP | Dynamic Code Blocks

      Automated restore of Live Company to Test Company in GP2013 CHANGES | Dynamic Code Blocks

      Jen has highlighted some of the things that need to be thought about, badly designed scripts is the most worrying, if someone has written some sql that includes the database name (I’ve seen this) in the SQL that is triggered off events in the UI etc, can then call back to the production database, potentially corrupting data. 


      Tim Wappat @timwappat
      United Kingdom
      Dynamics Code Blocks:

    • Rod Carlson


      December 19, 2016 at 8:37 AM

      Thanks very much, Everyone. I will read all of these materials and get prepared. 

      Rod Carlson
      Information Technology – Software Developer
      YMCA of Metro Chicago
      Chicago IL

    • Manuel Vasquez


      January 6, 2017 at 12:10 PM


      Do you have changes available as GP 2016 and R2 are now out. Should this newer sql script still work under GP 2016?

      Thank you,


      Manuel Vasquez
      Applications/Programmer Analyst
      Kings View Corporation
      Fresno CA

    • Beat Bucher


      January 9, 2017 at 8:57 AM


      There are no more recent script for this purpose AFAIK from Microsoft.. the one that was release for 2013R2 should work fine for 2015 & 2016.

      Beat Bucher
      Business Analyst, Dynamics GP MVP
      Ultra-Electronics Forensic Technology Inc.
      Montreal QC/Canada
      Montreal QC GPUG Chapter Leader
      GP2013R2 / MR2012 CU14

    DSC Communities replied 7 years, 6 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘Getting a fresh copy of the production DB applied to Test’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!