Request a topic or
contact an Arke consultant
July 2009

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments




The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2024

Loading a client’s CRM data locally

We had a client encountering a problem that the normal try-the-obvious-things via phone or email wasn’t able to solve, who had a problem in production that wasn’t showing up on their test system with test data, and whose production operation restrictions couldn’t let us work on the production system but could get us a database export of their CRM environment.

So, I set about getting their database backup to work well enough in a local environment that I could replicate the problem. 

Conveniently, this is exactly what the CRM's Deployment Manager "Import Organization" feature does.  Point it to the newly restored database, tell it how to map users in Active Directory, and it sets up the data for you.  Directions are at

If you need to try understanding what is happening at a lower level, you can do a similar procedure manually to get the UI working. NOTE that this approach definitely did not get a fully working environment, I quit adjusting things once I had an environment good enough for me to do my testing.  In particular, CRM async service crashes on a data not found error, so clearly at least one GUID isn’t updated as needed for fully correct functioning. So this procedure could not be used in production, but it’s good enough to replicate and test our scenario via the UI and SDK.  This is absolutely not supported, use at your own risk, stick to a clean environment that you can mangle safely, and try giving the "Import Organization" feature a shot first! 

  1. On a clean working CRM that you can mess up and break if necessary (e.g. ideally in a VM with its own active directory ‘just in case’), make a new CRM organization with a matching name/friendlyname as the CRM to restore.  This will make a organame_MSCRM database.  Rename that orgname_MSCRM database to something else for now (e.g. CLEAN_orgname_MSCRM).  Restore the client CRM database backup to the same name (change filesystem filenames to avoid an error on restore, renaming a database doesn’t change its files)
  2. We need to swap out GUIDs, but don’t need to know much about them in detail, so install a searchandreplace sproc in the restored db. I used this one:
    Modified to filter on: AND       DATA_TYPE IN ('uniqueidentifier')
    Can’t cut-and-paste it directly here due to author including an all-rights-reserved copyright, but he clearly states on the page to feel free to use it and modify it.
  3. Get the organization guids involved: select organizationid from organizationbase
    in both clean db and restored db
  4. To change GUIDs, we need to temporarily disable constraints.  This script is ‘good enough’; it throws some cannot alter errors that can be ignored.
  5. Make the restored database use guids from the clean database and clean up some other data

    -- replace organization guids:
    exec SearchAndReplace 'organizationid_restoredguid','organizationid_cleanguid';

    -- Fix system users ids so that an admin matches your own login
    update SystemUserBase set DomainName='[domain\user] ' where FullName='[client admin login name]';

    -- systemuserids, for their admin and my admin
    exec SearchAndReplace 'adminsystemuserid_restoredguid','mysystemuserid_cleanguid';

    -- Fix domain logins from CLIENTDOMAIN\ to local domain
    UPDATE SystemUserBase set DomainName=
    CASE WHEN (CHARINDEX('\',DomainName,0)>0) THEN '[localdomain]' + SUBSTRING(DomainName,CHARINDEX('\',DomainName,0),99999) 
                    ELSE DomainName

    -- update guids found in the organization table, on the left are values from client db, on the right values from clean db.
    -- user group id
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- privilege user group id
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- system user id
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- sql access group id
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- reporting group id
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- Modified by
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- integration userid
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- priv reporting group id
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- Base currency id
    exec SearchAndReplace 'restoredguid','cleanguid';
    -- handle a few strings that aren’t guids
    update OrganizationBase set PrivReportingGroupName='[cleanvalue]';
    update OrganizationBase set ReportingGroupName='[cleanvalue]';
    update OrganizationBase set SqlAccessGroupName='[cleanvalue]';
  6. Re-enable constraints, same script as before but replace ‘nocheck’ with ‘check’.
  7. Check your work:
    -- no error data should be printed out
  8. Restart CRM, log in (remember to enter the /OrgName URL), should be good with client data running in a local CRM!


Posted by David Eison on Wednesday, July 22, 2009 6:44 PM
Permalink | Comments (0) | Post RSSRSS comment feed