Dynamics GP – Automatically Update the Test Company with the Live Company Database

Throughout my career, I’ve received many support calls from clients asking to refresh their test company with the live database. This article will show you how to set up an automatic job in SQL and also schedule it to update periodically.

The prerequisites include:

• Owning full SQL instead of SQL Express (at the end of the article, I will mention how we can also do this with SQL Express with a few extra steps)
• The service account used to run the job must have db_creator and sysadmin roles (in order to restore the database)
• Service account must have full read and write access to the backup and data file location
• The test database(s) already created in Dynamics Utilities

Summary

There are five steps in the process:

1. Back up the live database
2. Restore the live database over the test database
3. Run the test company script – this will update any references from the database to the test database
4. Run the second part of the test company script
5. Schedule the backup

Step 1 : Back Up the Live database

Launch SQL Management studio (depending on your version of SQL the screens will appear slightly differently. Ideally log in as ‘sa’ or an administrator account with full access to the databases (test and live)

Right click on the live database > tasks > backup

Under the general tab, remove any back up to, and add a location to save the file. Please name the file a generic name, in this case TWOGOOD.bak

Under the media options, select the overwrite all existing backup sets

On the top, click on script and save to clipboard

This will be your first script. Save this script to notepad

The script should be similar to this:

Execute the script to ensure it’s working properly

You should receive a screen like the below if it’s working properly:

Step 2: Restore the Live Database Over Test

Navigate to the test database, right click > tasks > restore

Select device, not database

Locate the folder where the backup was stored

*** Ensure the restore to database is to the test company, in this case TWO8 *** Do not overwrite the live database

*** On the files tab, ensure the restore as is the test database name, not the live database name ***

Under the options tab – select ‘overwrite existing database’ and on some versions ‘close existing connections’

Press on the top button for script and save to clipboard, then copy that to another notepad

The script should look similar to this one:

Execute the script to ensure it’s working

You should receive a screen similar to this when it’s working properly:

Step 3: Test Company Script

In query manager, execute this script from Microsoft. It will adjust all the references from live to test.

Adjust the database name:

Execute the script and once it’s working the results should be below.  Save the script to a new note pad for the third script.

Step 4: Part Two of the Test Company Script

Execute this first script:

SELECT interid, CMPNYNAM, CMPANYID from dynamics..SY01500
The results should be similar to this screen:

Take note of the company ID for the test company. (In this case it’s 2)

The fourth script should be the following, replacing the database number:

Step 5: Schedule the 4 Scripts Saved in our Note Pad

If you own full SQL, you will have the SQL Server Agent where you can create steps and schedule the job

Expand SQL Server Agent. Under Jobs, right click and select ‘new job’

Under general, provide a name for this job

In the above example ‘Automate update of test company’ is used

Under the steps tab, we will create and copy and paste the four scripts

Under steps, select ‘new’

Provide a name, in the above example it’s named “Step 1: Backup’

Type will be Transact-SQL Script (T-SQL)
Database will be the test database

In the command, copy and paste the first script

Select ‘parse’ to confirm the script works

You will receive this message:

Select the advance tab and change the run as account to the service account

Repeat the above for Steps 2,3,4 to create four steps in total

The final screen should be similar to below:

Close and save the job, then right click and Start job at step 1

Highlight step 1 and select start

Step 6: Scheduling the Job

Inside the SQL management studio, under SQL Agent Jobs, select your new job and right click and select properties.

Under the schedule tab, you can create a new recurring task similar to Outlook’s calendar invite, depending on how often you’d want the test database refreshed. If you’d like to manually run the job to update the test database, you can also skip the schedule tab.

Notes about the above article:

I’ve ran into an issue with a client recently where the service account had full permissions but they still couldn’t restore the database. The work around is to force it to run as ‘sa’.

In order to do this, in the steps, change the type to ‘Operating System (Cmd Exec)

Adjust the script by adding this before the script:

sqlcmd -U sa -P SAPASSWORD -S SERVERNAME\INSTANCE -Q ”

and adding a “ at the end (placing the original script in between the quotation marks “ ” )

Similar to this example below:

This forces the scripts to be ran as ‘sa’ which usually has all the rights to backup and restore the database.

If you’re running SQL Express and it doesn’t have SQL Server Agent:

Add this to your four scripts:

sqlcmd -U sa -P SAPASSWORD -S SERVERNAME\INSTANCE -Q ” <SCRIPT> “

in notepad and save the file as a batch file eg. STEP1.bat

Repeat the Steps for 1-4 for four batch files. Then within Windows Scheduler, you can schedule the four tasks and batch files to run right after each other.

If you have any questions about this article or would like Encore to help assist in setting up automating the update of your test company, please feel free to contact us.

The post Dynamics GP – Automatically Update the Test Company with the Live Company Database appeared first on Encore Blog, by Encore Business Solutions

24 Wildly Creative Ways Companies are Using Dynamics CRM

Get the White Paper: 24 Wildly Creative Ways Companies are Using Dynamics CRM