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 http://support.microsoft.com/kb/952934
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!
- 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)
- 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: http://vyaskn.tripod.com/sql_server_search_and_replace.htm
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.
- Get the organization guids involved: select organizationid from organizationbase
in both clean db and restored db
- To change GUIDs, we need to temporarily disable constraints. This script is ‘good enough’; it throws some cannot alter errors that can be ignored. http://decipherinfosys.files.wordpress.com/2008/03/disable_fk_constraints.txt
- 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
END;
-- 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]';
- Re-enable constraints, same script as before but replace ‘nocheck’ with ‘check’.
- Check your work:
DBCC CHECKCONSTRAINTS
-- no error data should be printed out
- Restart CRM, log in (remember to enter the /OrgName URL), should be good with client data running in a local CRM!