Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Rod Carlson]

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?

Thanks

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

[Steve Burney]

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

——————————
Steve Burney
Senior Financial Systems Administrator
Correct Care Solutions
Nashville, TN United States
sburney@correctcaresolutions.com
615-312-7272
Twitter @SteveBurney
————————————————————————-

[Corey Clay]

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.

https://support.microsoft.com/en-us/kb/871973

——————————
Corey Clay
Senior Technical Consultant
NexTec Group
Louisville OH
————————————————————————-

[Jen Kuntz]

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

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

[T W]

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.

——————————
Tim Wappat @timwappat
Consultant
United Kingdom
Dynamics Code Blocks:
http://timwappat.info/
————————————————————————-

[Rod Carlson]

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]

Tim,

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

——————————
Manuel Vasquez
Applications/Programmer Analyst
Kings View Corporation
Fresno CA
————————————————————————-

[Beat Bucher]

Manuel,

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
+1-514-489-4267
@GP_Beat http://dyngpbeat.wordpress.com/
Montreal QC GPUG Chapter Leader
GP2013R2 / MR2012 CU14
————————————————————————-