When was the last time you refreshed your development environment?

Ours was refreshed last night and every night for the past month. Since our customers need their CRM upgrades thoroughly tested before the production upgrade, we used to manually refresh their development environments. A lot of changes can happen in a week or a month from the beginning of development to the production push, so I created a Powershell script to do it for me. Now, there is no more wondering if upgrades will work or not.

While it’s certainly possible to run this script from one location, to avoid issues with double-hop authentication issues in Powershell, I’ve opted to split the script into two: one running the SQL restore and another to import the organization on the CRM server.

Create the below script on your development SQL server.

First off we need to start logging and set up our variables. (You will need to create an anonymous SMTP relay if you don’t have one already.)

Next we need to delete the old copy and copy the latest production CRM SQL backup locally:

Since our CRM SQL backup file name is CRM_MSCRM_backup_2016_08_28_000001_8851794.bak, I want to rename it to just CRM_MSCRM:

We need to verify the copy was successful and set the subject for the email:

Now we can restore the database using the SQL Powershell cmdlets (NOTE: DO NOT run this script on a production SQL server since it will overwrite the existing CRM_MSCRM database):

We will use DBCC CHECKDB to verify the database is healthy, stored the results in a log file, create an attachment, cleanup old DBCC_CHECKDB logs, and send the email alert:

At the end of the script, we need to stop the transcript:

Save the script and create a scheduled task to run as often as needed using credentials that have sysadmin rights in SQL and rights to the folder paths specified in the variables section.

Create the below script on your development CRM front-end server.

First off we need to set up our variables:

We need to import the CRM Powershell module, disable and then remove the existing development org:

Once the old copy of production has been removed, we can import the newer CRM_MSCRM copy from earlier (NOTE: The “Sleep -s 120” command is to give CRM time to change the org from Pending to Enabled which may vary and I’m using the mapping option to map accounts based on Active Directory account name)

We need to verify it was successfully imported:

Once complete, we send a notification to the recipient address we set earlier with the results of the import:

Save the script and create a scheduled task to run after the previous SQL restore complete (mine runs 2 hours after the SQL restore) using credentials that has local admin rights.

After the script runs it will send 2 emails to the recipient email with the SQL copy results and if the import succeeded. These scripts also have the added benefit of being a great DR test for your CRM environment.

Like this post? Share it!