In Dynamics SL, one of the ways to establish a test environment with fairly low overhead is a duplicate set of your APP and SYS databases. While the arguement for a completely segmented test environment on additional servers can be made and is especially valid for upgrade processes – the duplicate databases are a good testing ground for interface customizations, stored procedure modifications, and quick query changes. Because these test databases are duplicates of your production data, the same data controls and permissions need to be exercised.

What does it take to get the test databases setup? With this method, you do need to be able to cover the additional storage space for the databases as well as space to store an additional set of full backups of your APP and SYS databases. Storage is cheap. If you can accommodate the space, a SQL agent job can keep your test environment in step with your current environment.

Test Databases

If you don’t already have a set of test databases on your production server – you’ll need to utilize the Database Maintenance application to create a set of empty databases.

I’m going to walk through the script that I run my server runs for me every month – but I don’t reccomend that you run it here and now. These are I/O intensive operations and could impact the performance of your production environment.

Capture Your Current Environment

Set aside a location – preferably a separate drive – to write a set of full backups to. It’s not a bad idea to understand what makes these backups special: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017#arguments

The script starts out executing against the master database. Where you see IECISAPP and IECISSYS, substitute in your production database names.

BACKUP DATABASE IECISAPP --YOUR PRODUCTION DB HERE, takes ~5min for 8GB
TO DISK = 'V:\backups_temp\DYNSL\applicationdb.bak'
WITH COPY_ONLY, INIT, SKIP;
GO

BACKUP DATABASE IECISSYS --YOUR SYSTEM DB HERE, takes <30 seconds for 50MB
TO DISK = 'V:\backups_temp\DYNSL\systemdb.bak'
WITH COPY_ONLY, INIT, SKIP;
GO

copy_only: the backup is independent of the regular backup series and ensures whatever we do here doesn’t mess with your current backup and recovery policies

init: this script is reused every month and the init keyword gives the server the ok to overwrite the same file again and again

skip: backup sets revolve around expiration dates -the skip keyword allows this script to be used to setup test databases at any time.

Overwrite the Test Databases

Before SQL server begins overwriting the test databases, it takes them offline for other users. After the restore completes, the database is set back to multi_user mode.

In our environment, the production databases are in the Full recovery model, but that’s overkill for the test databases. To keep from having to tend to log files for the test databases, we convert them to the Simple recovery model. Where you see TEST_IECISAPP and TEST_IECISSYS, substitute in your production database names.

-- UPDATES TEST DB HERE, takes ~3min
ALTER DATABASE [TEST_IECISApp] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST_IECISApp] FROM  DISK = N'V:\backups_temp\DYNSL\applicationdb.bak' WITH  REPLACE,  STATS = 5 
ALTER DATABASE [TEST_IECISApp] SET MULTI_USER
GO

ALTER DATABASE [TEST_IECISSys] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TEST_IECISSys] FROM  DISK = N'V:\backups_temp\DYNSL\systemdb.bak'   WITH REPLACE,  STATS = 5 
ALTER DATABASE [TEST_IECISSys] SET MULTI_USER
GO

ALTER DATABASE TEST_IECISAPP
SET RECOVERY SIMPLE;
GO

ALTER DATABASE TEST_IECISSYS
SET RECOVERY SIMPLE;
GO

USE [TEST_IECISApp]
GO
DBCC SHRINKFILE (N'IECISApp_Log' , 0, TRUNCATEONLY)
GO

Application Settings

Before the test databases are ready to use in the Dynamics SL client, a few application settings should be updated.

The databasename columns must be updated for the test system to function properly. We also update the cpnycolor column to provide a visual indicator to the user that they are in a test system.

USE TEST_IECISSYS;

UPDATE COMPANY SET DatabaseName = 'TEST_IECISApp', CpnyColor = '255' WHERE DATABASENAME = 'IECISApp'
UPDATE DOMAIN SET DatabaseName = 'TEST_IECISApp' WHERE DATABASENAME = 'IECISApp'

UPDATE DOMAIN SET DatabaseName = 'TEST_IECISSys' WHERE DATABASENAME = 'IECISSys'
GO

USE TEST_IECISAPP;
GO
ALTER VIEW [dbo].[vs_company] AS SELECT * FROM test_IECISSys..company

Set It and Forget It

Running this script as a SQL Agent Job that executes after the month-end is closed each month means that without lifting a finger or even having to remember, we have a current system to work in without potentially making breaking changes to production.