Library

Creating a test environment for AIT requires that you do more than simply duplicate databases (backup/restore) with a different database name and company name or creating test companies within a production system. Internally, both in the company data and in the DYNAMICS database, AIT stores database specific information. Because of this, creating a database with data copied from another database could copy records with invalid information. Also, creating an environment where database information is not truly identical is not a true test environment, because ANY set of differences can make a test fail that would otherwise pass in the live environment (and vice versa).

To create a true test environment, you must create a separate instance of SQL Server and duplicate ALL databases that exist in the live environment. This means it should have an identical version of the DYNAMICS database as well as ALL company databases.

Here are the steps to creating a test environment for AIT:

Create a new instance of SQL Server with a new DSN.
Log into the new DSN with Dynamics GP Utilities. This will create the appropriate DYNAMICS database, logins and stored procedures required for Dynamics GP.
Drop the DYNAMICS database in the new instance.
Detach the DYNAMICS and all company databases from the production instance. If you do not want to use Enterprise Manager or SQL Mamangement Studio and are comfortable with SQL Query, here is a sample script to detach multiple database quickly:

exec sp_detach_db 'DYNAMICS', 'true'
exec sp_detach_db 'TWO', 'true'
Copy the database files (.dbf and .log) from the Data folder of the production instance to the new instance.
Attach the copied database files within the new instance. If you do not want to use Enterprise Manager or SQL Mamangement Studio and are comfortable with SQL Query, here is a sample script to detach multiple database quickly:

exec sp_attach_db @dbname = N'DYNAMICS',
@filename1 = N'D:\MSSQL\Data\GPSDYNAMICSDat.mdf',
@filename2 = N'D:\MSSQL\Data\GPSDYNAMICSLog.ldf'

exec sp_attach_db @dbname = N'TWO',
@filename1 = N'D:\MSSQL\Data\GPSTWODat.mdf',
@filename2 = N'D:\MSSQL\Data\GPSTWOLog.ldf'
Create user logins in the new instance. We recommend that you do not use the 'sa' login for user testing in the test environment. Privileges associated with the 'sa' login do not provide a true parallel test environment. Because of this, you will need to manually create SQL logins in the new instance.
Synchronize the logins to the database Users. Logins are different from database users when you copy databases, even though the login name might be the same. You need to synchronize the database users to the new logins in order to run Dynamics GP. Use the sp_change_users_login stored procedure to connect the new logins to the copied users.

If you are using SQL 2008, you must use ALTER USER:

use DYNAMICS
ALTER USER lessonuser1 WITH LOGIN = lessonuser1

Here's a sample script if you are using SQL 2005 or lower:

use DYNAMICS
sp_changedbowner 'DYNSA'
sp_change_users_login 'Auto_Fix', 'lessonuser1'
sp_change_users_login 'Auto_Fix', 'lessonuser2'
use TWO sp_changedbowner 'DYNSA'
sp_change_users_login 'Auto_Fix', 'lessonuser1'
sp_change_users_login 'Auto_Fix', 'lessonuser2'

Finally, make sure that you use copies of the same dictionaries (including any forms and reports dictionaries) when running your tests.

After a separate instance and databases, you may want to automate the backup/restore processes for this test environment so that you can return to a clean slate, insuring that your data always starts without any corrupted data that might be created during your testing process. Using SQL Management Studio can be cumbersome for restoring if you have many databases, so we are including some samle scripts that can help make the backup/restore process easier.

If you use SQL Standard, you will have to backup/restore with the standard backup/restore commands for each database:

BACKUP DATABASE [DYNAMICS] TO DISK = N'C:\SQL\BACKUP11\DYNAMICS.bak'
WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 20,
NAME = N'[DYNAMICS]-Full Database Backup'

RESTORE DATABASE [DYNAMICS] FROM DISK = N'C:\SQL\BACKUP11\DYNAMICS.BAK'
WITH FILE = 1, NOUNLOAD , STATS = 20, RECOVERY , REPLACE ,
MOVE N'GPSDYNAMICSDat.mdf' TO N'C:\SQL\GPDATA11\GPSDYNAMICSDat.mdf',
MOVE N'GPSDYNAMICSLog.ldf' TO N'C:\SQL\GPDATA11\GPSDYNAMICSLog.ldf'

If you use SQL Enterprise, you can use snapshot databases to more quickly and easily restore databases to their original state. Note: you will not be able to use a standard restore process while a snapshot exists. If you need to refresh your data from a backup of your production data, you will need to drop all snapshots.

First create the snapshot databases:

CREATE DATABASE DynamicsSnapshot11 ON ( NAME = 'GPSDYNAMICSDat.mdf',
FILENAME = 'C:\SQL\UPDATE\GP11\BACKUP\GPSDYNAMICSDat11.ss' )AS SNAPSHOT OF DYNAMICS
Then restore them easily:

RESTORE DATABASE DYNAMICS FROM DATABASE_SNAPSHOT = 'DynamicsSnapshot11'
To refresh the snapshots, drop the snapshot and recreate it using the command in #1:

DROP DATABASE DynamicsSnapshot11

Creating A Test Environment

Quick Launch View All Site Content

Quick Launch
View All Site Content