We recently had a customer that was having an issue printing credit memos in Microsoft Dynamics GP.  We wanted to test the printing issue, but didn’t want to input test credit memos into a live production company.  How could we safely troubleshoot issues without compromising the customer’s live data?

Solution:

Make a copy of the production data and move it to a test company to allow us to test transactions without impacting the live company.

To accomplish this, we went into SQL server, did a backup of the production company and copied it to a test company.  Because all of our data (GL accounts, customers, vendors, items) and settings look like production, any test conducted in this test environment will mirror the production environment.

When else would this come in handy?

  • If the customer has a modification (of a report or window) they want to test
  • If a modified window or report is not working
  • If a customer wants to try a new module or function in Dynamics GP
  • Troubleshooting/testing a bug or bug fix

In our case, we were able to replicate the issue with credit memos not being applied correctly.  We then developed a fix and were able to test the fix in the test company before migrating it to production.

Steps to create a test company for safe troubleshooting:

The steps to accomplish this are quite simple.  Anyone with a basic knowledge of SQL server could do this in a relatively short period of time. In order to copy a production company to a test company, you will backup the production database and restore it to a test database.  Make sure all users are logged out of Dynamics GP before beginning this process.

1. Log into Microsoft SQL Server Management Studio (SSMS).

2. Select the production database for the company you want to copy.

Backup Dynamics GP


3. Right click on the database and choose Tasks>Backup.

4. Make sure Disk is selected, then click on Add.

5. Choose a location for the backup.  Be sure to note the location, you will need it again soon.

6. After the location is chosen, click OK to begin backing up the databaseBacking up Microsoft GP

7. When it is complete, go back to Object Explorer and choose the Test company database.

8. Right-click on the Test company database, and choose Restore.

9. Select Database.

Microsoft Dynamics GP Backup Process

10. For Source, choose the Production database you backed up earlier.  For Destination, choose the Test company database.

Restore Dynamics GP Backup

11. Click on Options and:

  • Check the box to Overwrite the existing database
  • Check the box to Close existing connections to destination database
  • Be sure to point to the file you created earlier

Restoring SQL database Microsoft Dynamics GP

12. Click on Files and check that the modified File name is using the database for the Test company.

13. When ready, click OK to begin the Restore.

When the Restore is completed, there are still a few more steps to go. If you don’t complete the following steps, when you try to log into the Test company, you will receive an error message.

14. Create a new query.

15. Run the following SQL statement—select * from sy01500—using the Dynamics database.

This will list all companies and the Company ID (CMPANYID) for each.  Since the Production database was copied to Test, the Company ID was also copied over and needs to be changed back to the Test Company ID.  Note the Company ID for the Test database.

SQL Query Backup

16. Run the following SQL statement—select * from sy00100—using the Test database.

Microsoft Dynamics GP SQL Query

17. To update the Company ID, run the following SQL statement—update sy00100 set CMPANYID=’xx’—where xx is the ID of the Test database.

When all of these steps are completed, you will be able to log into the Test company and it will contain an exact copy of the Production data.

And there you have it!  Just like that, you too can safely troubleshoot issues in Microsoft Dynamics GP.  Of course, if you’d like some more professional guidance on how to best extend and leverage Dynamics Great Plains, we’re here and ready to help.  Please feel free to contact us or schedule a consultation.

Additional Microsoft Dynamics GP Resources

Reconcile Misaligned Receivables Batch in Microsoft Dynamics GP

Tips for Importing Historical Invoice Data Into Dynamics GP

Microsoft Dynamics GP Upgrade Planning and Best Practices

Automate Purchase Order Management with Dynamics GP PO Generator