Sometimes, when making new tests on a development machine, it is necessary to empty the Service Catalog environment and start clean.
However, you may wish to leave other Service Management products intact on the database.
Here is a step-by-step guide to doing so.
Release : 17.2
Component : CA SERVICE CATALOG
These are the steps required to empty all Catalog data out of an MDB database, without either losing the tables relating to other Service Management products or needing to recreate the database from scratch. As with all direct database updates, please ensure you have taken a backup before proceeding.
Run any command-line scripts from a Service Catalog Command Prompt so all the necessary path and environment variable settings are correct.
1) Run the "uninstall MDB" script
This empties all the USM-related database tables but importantly does not delete them. If you want to see what it's doing, it calls usm_MDB_delete.dll, which is simply a big list of DELETE FROM commands for all the usm_* tables.
So you now have an empty database. If necessary, the log information for this step is in "%USM_HOME%\logs\install\usm_mdb_delete.log"
2) Next, you need to manually populate the Catalog default data into the database
"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\view\scripts\MDB\SQL"
"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\view\scripts\MDB_Leh\SQL"
"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\view\scripts\MDB_SantaFe\SQL"
"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\catalog\scripts\MDB\SQL"
"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\accounting\scripts\MDB\SQL"
Seed data loading writes its log to "%USM_HOME%\logs\install\seeddata.log"
3) In order to get subsequent scripts to run correctly, you need to tell scripts what release of Catalog you are. Run the SQL insert
insert into USM_SCHEMA_VERSION values ('17','0','0')
4) Then you need to manually re-apply SQL patches. Under the REPLACED folder there are a number of unconfigureMDB sql scripts, of the form
so for the example of 17.2.03, these are the following (there are no SQL updates in the 18.104.22.168 patch)
It is necessary to execute each of these in turn. If cutting and pasting from the .sql directly into an SQL Management Studio query window, you will see an error from the "exit" command at the end of the batch script; this is a normal side-effect of how they were originally run through the command line.
Running the command:
select * from usm_schema_version
will show that the system is now patched up to 17.2 again.
5) (optional) At this point, the Service Catalog system can be restarted, and is an entirely blank environment in terms of Service Offerings, request data, etc. If you are migrating other data back in, then this may be all you need. It is also the last time to make changes to the last ID values before data starts being created in the system.
However, if you need to recreate the default offerings that come out of the box, then you'll need to tell the system to redeploy them.
i) Launch the Service Catalog Setup Utility page.
ii) Skip straight to the Components tab - it is not necessary to redeploy the Database or Security (EEM).
iii) Set your Business Unit again, and click the Catalog and/or Accounting options, before selecting the content required.
iv) Click save, then finish.
Service Catalog will now restart, and you can log in to your clean system.