Backing 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 ();
Define the back up destination and switch to the Options page and set it to Overwrite all existing backup sets;
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;
BACKUP DATABASE [LIVE] TO
DISK = N'C:\SQL Server\SQL2008R2\Backup\LIVE.bak' WITH NOFORMAT, INIT,
NAME = N'LIVE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Next we need to generate the restore script (). On the General page select the backup from the location of the previous script;
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);
Once done click the Script button at the top of the window, which will produce code something like this;.
RESTORE DATABASE [TEST] FROM
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',
MOVE N'GPSLIVELog.ldf' TO N'C:\SQL Server\SQL2008R2\Data\GPSTESTLog.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
Now we have the above two scripts, we can begin creating the SQL Agent Job ();
Enter a Name, Category and Description and then click to the Steps page;
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;
Create a second step into which the restore script should be copied and pasted;
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;
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;
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;
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;
The Schedule I created above is to run the script at 0500 every week Monday morning with no end date;
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;
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.
10 thoughts on “Copy Microsoft Dynamics GP Live Company To Test Automatically”