How to Move the CA MDB Data from the Source to the Target Systems on Microsoft SQL Server - clone the machine

Document ID : KB000094722
Last Modified Date : 04/05/2018
Show Technical Document Details
Issue:
We will create SRVA clone of SITCH01C .
SITCH01C is the source and SRVA cthe target.
The requirement is to have the same version of Servicedesk and patches on SITCH01C and SRVA

1/ Database replication
The method consist of following the docops
How to Move the CA MDB Data from the Source to the Target Systems
https://docops.ca.com/ca-service-management/14-1/en/implementing/how-to-move-the-ca-mdb-data-from-the-source-to-the-target-systems
or
https://docops.ca.com/casm171/administering/moving-the-ca-mdb-data-from-the-source-to-the-target-systems/

2/ Custom file replication
Copy of the customized application files from SITCH01C to SRVA
We use
pdm_backup -g -v -f folder_name
and
pdm_restore -g -v -f folder_name
Environment:
Windows 2008r2
Windows 2012
Service Desk 12.9 , 14.1 and 17.1 (the same method since 12.9)
We have to move the mdb from SITCH01C to SRVA
With the words of the docops, SITCH01C is the source and SRVA the target
SITCH01C and SRVA are running the same version of ServiceDesk, same version of mdb
Cause:
When we clone the mdb from one machine to another, we need to keep the original unimpacted table created by the installer, If we restore the unimpacted table from another installation, pdm_configure will return errors.
Resolution:

mplementation
1/ How to Move the CA MDB Data from the Source to the Target Systems
https://docops.ca.com/ca-service-management/14-1/en/implementing/how-to-move-the-ca-mdb-data-from-the-source-to-the-target-systems

2/ Unimpacted Configuration Tables
https://docops.ca.com/ca-service-management/14-1/en/implementing/how-to-move-the-ca-mdb-data-from-the-source-to-the-target-systems/unimpacted-configuration-tables

3/ Move the CA MDB Database on Microsoft SQL Server
https://docops.ca.com/ca-service-management/14-1/en/implementing/how-to-move-the-ca-mdb-data-from-the-source-to-the-target-systems/move-the-ca-mdb-database-on-microsoft-sql-server
 

The steps consist of
- backup the mdb from the source on SITCH01C
- save the application files on SITCH01C to sd_source folder (using pdm_backup -g)
- backup the unimpacted tables on the target on SRVA
- restore thd mdb from source to target from SITCH01C to SRVA
- restore on the target the original unimpacted tables of the target on SRVA
- restore the application files from SITCH01C to SRVA (use pdm_restore -g)
- pdm_configure on SRVA


The detailed operations are in this docops
<<Move the CA MDB Database on Microsoft SQL Server>>
https://docops.ca.com/ca-service-management/14-1/en/implementing/how-to-move-the-ca-mdb-data-from-the-source-to-the-target-systems/move-the-ca-mdb-database-on-microsoft-sql-server


STEPS

SITCH01C

On source, backup the mdb database and site\mods
sqlcmd -d mdb -U sa -P Password1 -Q "backup database mdb to disk='c:\mdb_source.bak';"
pdm_backup -g -f sd_source

User-added image

SRVA

On target, backup the unimpacted tables

bcp al_cdb_comp_installstate_bkup out "al_cdb_comp_installstate_bkup.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp al_cdb_componentinstallstate out "al_cdb_componentinstallstate.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp al_cdb_config_params_backup out "al_cdb_config_params_backup.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp al_cdb_configurationparameters out "al_cdb_configurationparameters.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp al_cdb_files out "al_cdb_files.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp ca_application_registration out "ca_application_registration.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp options out "options.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp usp_servers out "usp_servers.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp sapolicy out "sapolicy.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp ci_mdr_provider out "ci_mdr_provider.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp usp_domsrvr out "usp_domsrvr.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp usp_configuration out "usp_configuration.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp usp_webeng_alias out "usp_webeng_alias.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp usp_webeng_domsrvr out "usp_webeng_domsrvr.bcp"  -N -S srva -d mdb -U sa -P Password1
bcp usp_webengine out "usp_webengine.bcp"  -N -S srva -d mdb -U sa -P Password1
User-added image
User-added image
User-added image
User-added image
User-added image

On the target, restore the mdb from source

sqlcmd -d master -U sa -P Password1 -Q "restore database mdb from disk='c:\mdb_source.bak' with recovery, replace;"
sqlcmd -d mdb -U sa -P Password1 -Q "sp_change_users_login 'AUTO_FIX','ServiceDesk';"
sqlcmd -d mdb -U sa -P Password1 -Q "sp_change_users_login 'AUTO_FIX','mdbadmin';"
User-added image

pdm_restore -g -v -f sd_source
User-added image
User-added image


On the target, delete the data of the unimpacted table of the source

sqlcmd -d mdb -U sa -P Password1 -Q "delete from al_cdb_comp_installstate_bkup"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from al_cdb_componentinstallstate"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from al_cdb_config_params_backup"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from al_cdb_configurationparameters"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from al_cdb_files"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from ca_application_registration"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from options"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from usp_servers"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from sapolicy"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from ci_mdr_provider"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from usp_domsrvr"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from usp_configuration"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from usp_webeng_alias"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from usp_webeng_domsrvr"
sqlcmd -d mdb -U sa -P Password1 -Q "delete from usp_webengine"
User-added image
User-added image


On the target, restore the initial data of the unimpacted tables

bcp mdb.dbo.al_cdb_comp_installstate_bkup in "al_cdb_comp_installstate_bkup.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.al_cdb_componentinstallstate in "al_cdb_componentinstallstate.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.al_cdb_config_params_backup in "al_cdb_config_params_backup.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.al_cdb_configurationparameters in "al_cdb_configurationparameters.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.al_cdb_files in "al_cdb_files.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.ca_application_registration in "ca_application_registration.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.options in "options.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.usp_servers in "usp_servers.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.sapolicy in "sapolicy.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.ci_mdr_provider in "ci_mdr_provider.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.usp_domsrvr in "usp_domsrvr.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.usp_configuration in "usp_configuration.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.usp_webeng_alias in "usp_webeng_alias.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.usp_webeng_domsrvr in "usp_webeng_domsrvr.bcp"  -N -S srva -U sa -P Password1
bcp mdb.dbo.usp_webengine in "usp_webengine.bcp"  -N -S srva -U sa -P Password1
User-added image
User-added image
User-added image
User-added image
 

pdm_restore -g -v -f sd_source
User-added image
User-added image
 

execute pdm_configure on SRVA

We should not see any warning in pdm_configure.
User-added image
User-added image
User-added image
User-added image
User-added image
User-added image
User-added image
User-added image
User-added image
User-added image
User-added image
User-added image

End of cloning

Additional Information:

This tecdoc is cloning machine with just ServiceDesk.
If the mdb is shared by the other applications as Service Catalog,..., we would have to save and restore the other unimpacted tables listed in this link

Unimpacted Configuration Tables per Product
https://docops.ca.com/ca-service-management/14-1/en/implementing/how-to-move-the-ca-mdb-data-from-the-source-to-the-target-systems/unimpacted-configuration-tables