Copy Microsoft Dynamics GP Live Company To Test Automatically

Microsoft Dynamics GPBacking up the a Microsoft Dynamics GP company to test is, unfortunately, not as simple as backing up one database and restoring it to the Test one. There are two scripts which need to be run after doing so; the first changed the INTERID and Company Name and the second changes the database owner to dynsa.

At my last company, we decided to make this process as easy as possible for clients, so we started creating a SQL Agent Job which would do the backup, restore and run the scripts with minimal effort or could even run on a scheduled basis. The basic purpose was to give the client a test or training system which was always, or could very quickly be, up to date.

The first step is to generate a script to backup the database. To do this open up the Back Up Database window in Management Studio (Object Explorer » right click the database to be backed up (in this example the LIVE database) » Tasks » Backup);

Back Up Database - General page

Define the back up destination and switch to the Options page and set it to Overwrite all existing backup sets;

Back Up Database - Options page

Click the script button at the top of the form and it will generate a script into a new Query window which will look something like this;

DISK = N'C:\SQL Server\SQL2008R2\Backup\LIVE.bak' WITH NOFORMAT, INIT,

Next we need to generate the restore script (Object Explorer » right click the database to be backed up » Tasks » Restore » Database). On the General page select the backup from the location of the previous script;

Restore Database - General page

Next switch to the Options page and check Overwrite the existing database (WITH REPLACE). The Restore As path of the database files may need to change. In this example the Restore As is GPSLIVEDat.mdf and GPSLIVELog.ldf; we need to change this to GPSTESTDat.mdf and GPSTESTLog.ldf (do not change the Original File Name);

Restore Database - Options page

Once done click the Script button at the top of the window, which will produce code something like this;.

DISK = N'C:\SQL Server\SQL2008R2\Backup\LIVE.bak' WITH FILE = 1,
MOVE N'GPSLIVEDat.mdf' TO N'C:\SQL Server\SQL2008R2\Data\GPSTESTDat.mdf',

Now we have the above two scripts, we can begin creating the SQL Agent Job (Object Explorer » SQL Server Agent » right click Jobs » New);

New Job

Enter a Name, Category and Description and then click to the Steps page;

New Job - Steps Needed

There are four steps to this Job which we will create, starting with the create database step. Give the Step a name and copy and paste the backup script produced above into the Command box and click OK;

New Job Step - Backup LIVE Database

Create a second step into which the restore script should be copied and pasted;

New Job Step - Restore LIVE Database Into TEST

The third step, is the on which updates the Company ID and Name on all the relevant tables. This script is a Microsoft supplied one which can be downloaded from the Microsoft Support Site; KB Article ID: 871973 (Set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, SQL Server 2005, or SQL Server 2008). Copy and paste this into the Command box, select the Database to run it again (TEST in this example) and click OK;

New Job Step - Update Company ID And Name

The fourth script, also supplied by Microsoft on the, above, KB Article changes the database owner to DYNSA. Enter a Name, select the required database (the same one as in the previous step; TEST), paste the script into the Command box and click OK;

New Job Step - Change db_owner to DYNSA

With the forth step completed the Job can be saved at this point if it is going to be run only on an ad hoc basis;

New Job - Steps Added

If, however, the script is to be scheduled to be run on a regular basis, switch to the Schedules page and create a New Job Schedule;

New Job Schedule

The Schedule I created above is to run the script at 0500 every week Monday morning with no end date;

New Job Complete

After saving the new Job, I ran a manual test to ensure there were no bugs and then left it to run on it’s schedule;

Agent Job Progress

As this Agent Job is backing up and restoring databases, please be careful with which database you restore over the top of.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.