How to Move the CA MDB Data from the Source to the Target Systems on Microsoft SQL Server (used for cloning machine)

Document ID : KB000007983
Last Modified Date : 03/05/2018
Show Technical Document Details
Issue:

We will create SRVA clone of SITCH01C .
The requirement is to have the same version an same patch level of ServiceDesk 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
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:

Implementation
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
source backup.png


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
target_save_unimpacted_table_01.png

target_save_unimpacted_table_02.png

target_save_unimpacted_table_03.png

target_save_unimpacted_table_04.png

target_save_unimpacted_table_05.png

 

 

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';"
target_restore_source_mdb.png

pdm_restore -g -v -f sd_source

target_restore_site_mod_from_source_01.png

target_restore_site_mod_from_source_02.png

 

 

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"
target_delete_unimpacted_table_from_source_01.png

target_delete_unimpacted_table_from_source_02.png

 

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
target_restore_initial_unimpacted_table_01.png

target_restore_initial_unimpacted_table_02.png

target_restore_initial_unimpacted_table_03.png

target_restore_initial_unimpacted_table_04.png

pdm_restore -g -v -f sd_source

target_restore_site_mod_from_source_01.png

target_restore_site_mod_from_source_02.png

 

 

execute pdm_configure on SRVA

We should not see any warning in pdm_configure.

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