How I create TEST and DEV environments using T-SQL

By Val Gameiro posted 12 days ago

  
As an in-house developer, I always make sure we have 3 databases total: LIVE, TEST and DEV.

I do my development in DEV, move the changes over to TEST so Accounting can give their stamp of approval, and finally I move it all to LIVE.

I only use 1 SQL server, although it'd be preferable to have 3 separate servers if you can squeeze it.

When I'm ready to refresh the TEST or DEV environments, I take the following steps:

1. Backup the NAV Objs in (TEST/DEV) from the Object Designer. I always put the company and database in the name, e.g. BPL All TEST Objs 2017-11-10.fob, and then I compress with 7zip (most efficient compression tool IMHO). I keep these object backups in an external hard drive, just in case.

2. I run a SQL backup from SQL Management Studio, using the interface, or I use one of the latest nightly backups.

3. Then I restore said backup into the appropriate database (TEST/DEV) using T-SQL:
[code]
USE master
ALTER DATABASE [DCI Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [DCI Test] FROM DISK = 'E:\SQL Backup\dcilive_backup_2017_06_07_010001_3465036.bak'
GO
ALTER DATABASE [DCI Test] SET MULTI_USER;
GO
[\code]

Where the path\filename after FROM DISK matches the database in Step 2, and the database name [DCI Test] matches the name of the database I want to use.

4. Once the backup is restored, I start up the client, and go to the Company Information page, and change the System Indicator Style to Accent3 for the TEST environment (GREEN) and Accent1 for the DEV environment (RED).

5. Then I go into the Companies page, and rename the company to something like: TEST BPL Plasma 2017-11-10, so I always know how far the "real" data goes in this particular database, and can see how long it's been since the data was refreshed. If I get an error about one of the tables, I do it again until the error goes away.

6. I close the client and start it up again, and I'm all set.

Hope that helps!
1 comment
69 views

Permalink

Comments

11 days ago

Thanks for sharing @Val Gameiro this is a great article!​