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.)
# Start logging $Date = get-date -f MM-dd-yyyy $LogFile = "<log path>\Dev_Environment_Refresh_" + $Date + ".log" Start-Transcript -path $LogFile -Append -Verbose Write-Verbose -Message "=== START SCRIPT ====" # Set up Variables $emailserver = "<Your SMTP relay IP>" $sender = "<sender email>" $recipient = "<recipient>" $subject = "CRM Development Environment Refresh Results" $hostname = hostname $tempBackupPath = "<temp storage for SQL backup>" $SourcePath = '<location of Production CRM SQL Backups>' $DestinationPath = $tempBackupPath+'\'
Next we need to delete the old copy and copy the latest production CRM SQL backup locally:
# Delete existing file Write-Verbose -Message "=== Deleting old copies ====" Remove-Item -Path $tempBackupPath\CRM_MSCRM.bak -Force -ErrorAction SilentlyContinue # Specify UNC path not network share where backup files has to be copied $DestinationPath = $tempBackupPath+'\' # List recent file (only one) within sub-directories Write-Verbose -Message "=== Getting Latest CRM Backup ====" $RecentFile = Get-ChildItem -Path $SourcePath -Filter "*.bak" | Sort-Object LastAccessTime -Descending | Select-Object -First 1 # Copy the backup file Write-Verbose -Message "=== Copying Backup files to destination ====" Copy-Item -Path $RecentFile.FullName -Destination $DestinationPath -Force
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:
# Trim the date time from the copied file name, store in a variable Write-Verbose -Message "=== Renaming Copied SQL Backups ====" # CRM File Rename $DestinationFile = $DestinationPath+$RecentFile.Name $RenamedFile = ($DestinationFile.substring(0,$DestinationFile.length–37))+'.bak' #Rename the copied file Write-Verbose -Message "=== Renaming backup to CRM_MSCRM.bak ====" Rename-Item $DestinationFile $RenamedFile
We need to verify the copy was successful and set the subject for the email:
#Check for copied files Write-Verbose -Message "=== Verifying SQL Backup Copy was successful ====" $modifiedFiles = Get-ChildItem $tempBackupPath\CRM_MSCRM.bak | Where{$_.LastWriteTime -gt (Get-Date).AddDays(-365)} | select Name #Set subject of email alert if ($modifiedFiles) { $body = "$hostname - Copy of Prod CRM Database Complete" Write-Verbose -Message "=== CRM SQL Backup Copy was successful ====" } else { $body = "$hostname - Copy of Prod CRM Database FAILED" Write-Verbose -Message "=== CRM SQL Backup Copy FAILED ====" }
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):
## Import SQL PS module Import-Module sqlps –DisableNameChecking # Restore Prod copy over Dev instance - Note: Query Timeout must be set to 0 in SQL 2012 so the commands won't timeout Write-Verbose -Message "=== Restoring CRM Database ====" Invoke-Sqlcmd -Query "ALTER DATABASE CRM_MSCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -ServerInstance "$hostname" -QueryTimeout 0 Invoke-Sqlcmd -Query "RESTORE DATABASE CRM_MSCRM FROM DISK = '$tempBackupPath\CRM_MSCRM.bak' WITH RECOVERY, REPLACE" -ServerInstance "$hostname" -QueryTimeout 0 Invoke-Sqlcmd -Query "ALTER DATABASE CRM_MSCRM SET MULTI_USER" -ServerInstance "$hostname" -QueryTimeout 0
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:
# Verify Restore Write-Verbose -Message "=== Verifying CRM Database Restore ====" $CRM_MSCRM_Status = Invoke-Sqlcmd -Query "DBCC CHECKDB (CRM_MSCRM)" -ServerInstance "$hostname" -QueryTimeout 0 -Verbose 4>&1 # Save DBCC CHECKDB results to a file Write-Verbose -Message "=== Output CRM Restore Verification to File ====" 'CRM_MSCRM Status' + "`r`n" + '==============================' + "`r`n" + $CRM_MSCRM_Status | Out-File $sqlLogPath\CRM_MSCRM_DBCC_CHECKDB_Results_$(get-date -f yyyy-MM-dd).txt # Add to attachments Write-Verbose -Message "=== Attaching CRM Restore Verification File to Email ====" $attachments = @() $attachments += "$sqlLogPath\CRM_MSCRM_DBCC_CHECKDB_Results_$(get-date -f yyyy-MM-dd).txt" # Delete old DBCC CHECKDB logs older than 5 days Write-Verbose -Message "=== Deleting old DBCC CHECKDB logs older than 5 days ====" Get-ChildItem -Path $sqlLogPath -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force # Send email alert Write-Verbose -Message "=== Sending Email ====" Send-MailMessage -To $recipient -Subject $subject -From $sender -Body $body -SmtpServer $emailserver -Attachments $attachments Sleep -Seconds 20
At the end of the script, we need to stop the transcript:
# End Logging Write-Verbose -Message "=== END SCRIPT ====" Stop-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:
# Email Settings $youremailserver= "<anonymous SMTP relay>" $sender = "<sender email address>" $recipient = "<recipient email address>" # Development SQL Server FQDN $SQLServer = "<FQDN of development SQL server" # Development Org Name $orgName = "<development org name - ex. crmdev.eimagine.com>" #Set hostname to CRM front-end $hostname = hostname
We need to import the CRM Powershell module, disable and then remove the existing development org:
# Add CRM PS Module Add-PSSnapin Microsoft.Crm.PowerShell # Disable the existing CRM org Disable-CrmOrganization $orgName # Remove the existing CRM org Remove-CrmOrganization $orgName
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)
# Import the latest copy of production CRM Import-CrmOrganization -SqlServerName $SQLServer -DatabaseName CRM_MSCRM -SrsUrl "https://$SQLServer/ReportServer" -DisplayName $orgName -Name $orgName -UserMappingMethod ByAccount Sleep -s 120
We need to verify it was successfully imported:
# Check state of imported Org $importedOrg = Get-CrmOrganization -Name $orgName $Body = Get-CrmOrganization -Name $orgName | select DatabaseName,FriendlyName,State,SQLServerName,Version | Out-String $importedOrgState = $importedOrg.State | Out-String
Once complete, we send a notification to the recipient address we set earlier with the results of the import:
# Set subject of email alert if ($importedOrgState.contains("Enabled")) { $subject = "$hostname - Import of $orgName Successful" } else { $subject = "$hostname - Import of $orgName FAILED" } # Send email alert Send-MailMessage -To $recipient -Subject $subject -From $sender -Body $Body -SmtpServer $Youremailserver Sleep -s 20
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.