CRM Refresh using PowerShell

We just started using CRM and wanted to get rid of the manual process of refreshing CRM from Production to QA\Dev for our developers.

Our environments

Developer Boxes
We have 14 dev servers running on Windows 2012 R2.
These boxes run SQL 2012 and CRM 2015.

Dev Servers
We also have 2 Dev Environments that each have a CRM 2015 application server running on Windows 2012 R2 and a SQL 2012 Server.

QA Servers
We have 1 QA environment that is setup identical to the Dev environment and we have 1 QA environment that has 2 CRM 2015 application servers with one SQL 2012 server and one SSRS 2012 server.

Production Servers
 2 CRM 2015 application servers on Windows 2012 R2, 1 SQL 2012 Always On availability group and 1 SSRS Always On availability group. Then we have the same configuration at our Disaster Recovery site.

So with all these different environments we have been asked to refresh them periodically. The all in one developer servers are pretty straight forward since all of the software is installed you do not need to add install additional software.

MSCRM_CONFIG Hack

Proceed with Caution

We found a bug when moving between the various envrionments. Example: If you take the production database backup and restore it to  your DEV environment to create an Organization, then you try to create another Organization with that same backup you will receive the follow error during import:

Could not find stored procedure ‘p_CreateAuditEntry’

We were trying to setup multiple Organizations on a DEV instance to test various upgrades. In order to get around this we found a solution on this blog: http://www.powerobjects.com/2015/04/29/error-importing-organization-after-update-0-1/

The below scripts are a hack that you should NEVER do in a Production environment. Modifying the MSCRM_CONFIG database void your Microsoft support agreement.

Prerequisites for Refresh

• The account you use to do the refresh needs to be a Deployment Administrator and have admin permissions on the application server and the SQL server. 
• It also needs sysadmin rights in SQL and needs to be the same account that runs the "CRMDeploymentServiceAppPool" in IIS. 
• For Multi-Tier Installs (dedicated App server and SQL Server) Install below files on the Application server:
• 1.Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
• 2.Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
• 3.Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)
• UserMapping.xml file example. This is needed if you are mapping a Production install account to your Dev environment. We use separate accounts for our PROD, DEV, and QA environments. See below for sample file.

Refresh Script 

The below script we wrote after doing a pretty good amount of research and not being able to find all the information we need in one location. Please use this with care we are not responsible for your use\misuse. Open PowerShell as Administrator. I suggest saving the below file as a .ps1 and then executing it from PowerShell.