How to Use PowerShell to Refresh a Dynamics CRM Organization

For this demonstration, I’ll be using Dynamics CRM 2016SQL Server 2012 running on Window Server 2012 with PowerShell 3.0 and the SQL Server 2012 PowerShell commandlet module, SQLPS.  These commands will also work on Dynamics CRM 2013 and 2015, SQL Server 2014 and PowerShell 4.0.

Backup the SQL Database

  1. Before attempting any PS commands the, log onto the SQL Server for your Dynamics CRM instance and run Windows PowerShell as the Administrator and enter the command, Get-Host
    2016-02-05_11h09_32
  2. Next, Confirm the execution policy for the CurrentUser using the command, Get-ExecutionPolicy -Scope CurrentUser
    2016-02-05_11h11_30
  3. For this demonstration, since the execution policy is set to “Undefined”, I’m going to change it to “Unrestricted” using the command, Set-ExecutionPolicy -ExecutionPolicy “Unrestricted”
    2016-02-05_11h15_45
  4. Confirm that you have the SQL Server PowerShell Commandlets module SQLPS installed, check the default folder, C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS. You can download the SQL Server 2012 Feature Pack from here.
  5. In PowerShell, load the SQLPS module using the command, Import-Module SQLPS
    2016-02-05_11h26_45
  6. To help make finding SQL Server related objects and names, create an object variable named, $smo to the object, SqlServer.Management.Smo.Server using the command, $smo = New-Object ‘Microsoft.SqlServer.Management.Smo.Server’2016-02-05_11h32_32
  7. Confirm that the $smo object was populated correctly using the command, $smo.Settings
    Certain settings values will come in handy later on, e.g. BackupDirectory and DefaultLog
    2016-02-05_11h34_48
  8. To return a list of the SQL databases, use the command, $smo.Databases
    2016-02-05_11h39_43
  9. To make a SQL backup of the database named, B_MSCRM, use the command,
    Backup-SqlDatabase -ServerInstance $smo.Name -Database “B_MSCRM” -BackupFile ($smo.BackupDirectory + “\C_MSCRM.bak”) -CompressionOption “On”

    Note, you will need to close CRM Deployment Manager before to execute the backup command.
    2016-02-05_11h57_45Notice the $smo object variables, $smo.Name which returns the server name, and $smo.BackupDirectory which returns the file path to the Backup folder.   Make sure the folder permissions are set to allow for writing to whatever folder you choose.  Note, the attribute, -CompressionOption is not required but recommended to save space.

    A progress bar may appear at the top of the PowerShell window
    2016-02-05_11h47_21

  10. Using Windows Explorer, confirm that the backup file was completed.
    2016-02-05_12h00_18

 


Restore the SQL Database

  1. In this demonstration, I’ll be restoring the database to the same SQL Server. In PowerShell, repeat the steps 2 through 6 in the previous section.
  2. A PowerShell collection variable containing the new file locations of the new SQL Data and Log First, create variables to store the SQL Data and Log file names.
    2016-02-05_12h44_05
    Second, create the collection variable to store the objects, Microsoft.SqlServer.Management.Smo.RelocateFile
    2016-02-05_12h53_28
  1. To restore the database, use the command,
    Restore-SqlDatabase -ServerInstance $smo.Name -Database “C_MSCRM” -BackupFile ($smo.BackupDirectory + “\C_MSCRM.bak”) -RelocateFile $sql

    2016-02-05_13h02_01

    Notice the $smo object variables, $smo.Name which returns the server name, and $smo.BackupDirectory which returns the file path to the Backup folder.

    A progress bar may appear at the top of the PowerShell window
    2016-02-05_13h00_11

  2. Using SQL Server Management Studio, confirm that the database was restored.
    2016-02-05_13h04_23

 


Import the CRM Organization

  1. Before you attempt to import the new organization, you will need to know the Web Service URL of the SQL Server Reporting Services. If you do not know the Web Service URL of SSRS, open the Reporting Services Configuration Manager and open the Web Service URL page.
    2016-02-05_13h26_58
  2. If the new CRM organization is connected to a different domain or users no longer exist. You will also need to have an XML file containing the user mapping values.  One can be generated using the CRM Deployment Manager.  Select the CRM Organization Name and click on Import Organization…  Go through the wizard and on the Select Method for Mapping Users page, select the option for Custom Mapping Options
    2016-02-05_13h31_55
    On the following page, select the option to Generate a new mapping file and choose a filename and location.
    2016-02-05_13h34_04
     
    Open the XML file in Notepad and make the necessary changes.
    2016-02-05_13h29_58

    ***Important! The user performing the import must have a System Administrator role assigned to the same username on the new CRM organization.  The user must be able to access the SSRS.

  3. Depending on the user assigned to the Deployment Web Service, the same user must be in the ReportingGroup {<Guid>} group in Active Directory. In this demonstration, the Deployment Web Service has a separate user account assigned to it.
    2016-02-05_13h54_04
    The user account, crm-deployment needs to be a member of the ReportingGroup {<Guid>} group.
    2016-02-05_13h57_35
  4. Log onto the CRM web server where you will be importing the new organization and in PowerShell, load the Dynamics CRM PowerShell Snapin, use the command, Add-PSSnapin Microsoft.Crm.Powershell
    2016-02-07_13h53_24
  5. In PowerShell, to import the new organization using the user-map.xml, use the command,
    Import-CrmOrganization -SqlServerName $smo.Name -DatabaseName “C_MSCRM” -SrsUrl “http://win2012/ReportServer” -DisplayName “C” -Name “C” -UserMappingMethod ByMappingXml -UserMappingXml “C:\Temp\user-map.xml”

    If a user mapping is not needed and you are sure the accounts for the new organization are on the same domain, use the UserMappingMethod attribute value, ‘ByAccount’. Use the command,

    Import-CrmOrganization -SqlServerName $smo.Name -DatabaseName “C_MSCRM” -SrsUrl “http://win2012/ReportServer” -DisplayName “C” -Name “C” -UserMappingMethod ByAccount
  6. If everything is works, a Guid of the OperationId should be returned. See the troubleshooting section if you run into an error message.
    2016-02-07_09h00_10
  7. To view the status of the operation, use the command, Get-CrmOperationStatus -OperationId <Guid>
    2016-02-07_09h10_50

 


Troubleshooting Import-CrmOrganization Errors

The current user does not have required permissions (read/write) for the following Active Directory group: CN=ReportingGroup {<Guid>}

2016-02-07_09h14_58

  1. In IIS, see which user account is running the Application Pool, CrmDeploymentServiceAppPool
    2016-02-07_09h32_51
  2. In Active Directory, confirm that the CrmDeploymentServiceAppPool identity user is a member of the AD group ReportingGroup {<Guid>}
    2016-02-07_09h36_14

 

 

The SELECT permission was denied on the object ‘OrganizationBase’, database ‘B_MSCRM’, schema ‘dbo’.

2016-02-05_23h04_28

  1. Open SSMS and connect to the CRM SQL Server. Expand Security > Logins and then right-click NT AUTHORITY\NETWORK SERVICE user and select Properties.
    2016-02-05_23h05_56
  2. In the Login Properties window, open the User Mappings page and check the database mapping and select db_owner permissions.
    2016-02-05_23h08_04

One thought on “How to Use PowerShell to Refresh a Dynamics CRM Organization”

  1. I need to import a CRM organization from another computer using Invoke-command and I can’t get it to work unless I pass credentials. is there another way to do this.

    [ScriptBlock] $global:ImportOrg = {
    param ()
    $sqlSnapin = Get-PSSnapin | where {$_.Name -eq “Microsoft.Crm.PowerShell”}
    if($sqlSnapin -eq $null)
    {
    Add-PSSnapin Microsoft.Crm.PowerShell
    }
    $ConfirmPreference=”None”
    $ConfirmPreference
    Import-CrmOrganization -SqlServerName “XXXXXXX” -DatabaseName “XXXXX” -SrsUrl “http://XXXXXX/ReportServer” -DisplayName “XXXX” -Name “XXXX” -Timeout 180 -UserMappingMethod “KeepExisting” -Verbose
    }
    #cls
    $Password = ConvertTo-SecureString ‘XXXX’ -AsPlainText -Force
    $Creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList ‘XXXX’, $Password

    $sess = New-PSSession -ComputerName ‘XXX’ -Credential $Creds
    Invoke-Command -Session $sess -ScriptBlock $ImportOrg

    remove-PSSession $sess

Leave a Reply

Your email address will not be published. Required fields are marked *