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
There are five steps in the process:
- Back up the live database
- Restore the live database over the test database
- Run the test company script – this will update any references from the database to the test database
- Run the second part of the test company script
- 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.
Webinar - Dynamics GP Coffee Break | Maintenance of SOP Tables
Find out what happens in SOP to cause orphan records, and why we care. Learn about removing these transactions and how that affects inventory allocations.
9:00 am – 9:25 am PST
Webinar - Maintenance of SOP Tables in GP
9:00 am – 9:25 am PST