Turning a production database into a staging database

The idea here is to backup the production database and restore the database on the staging server, overwriting the existing staging database. A side effect of this alternative is that some transactional data, such as user registration data, order data is restored onto the staging database. This data is not usually necessary on the staging server, and you might want to clean up this data to save disk space.

To backup your production database and restore it as your staging database:

  1. Stop the WebSphere Commerce server on the staging server.
  2. Backup the staging database in case it is required later.
  3. Create an offline backup of the production database
  4. Restore the production database backup on the staging server, overwriting the staging database.
  5. Create staging triggers in the staging database, by connecting to the database and running the script in
    DB2 wc_installdir\schema\db2\wcs.stage.trigger.sql
    Oraclewc_installdir\schema\oracle\wcs.stage.trigger.sql
  6. Update the KEYS table in the staging database to avoid primary key conflict between the staging and production servers. For example, the following SQL statement updates the KEYS table:
    update keys set counter=40000000000000000000 , prefetchsize=500 , lowerbound=4000000000000000000 , upperbound=9223372036850000000 where tablename in ('trading', 'buyerpo', 'catencalcd', 'catgpcalcd', 'participant', 'termcond', 'pattrvalue', 'attachment', 'mbrattrval', 'offer', 'tradeposcn') The key values may vary in different versions of WebSphere Commerce. You may need to modify the above SQL to reflect the correct key values. For the correct key values, refer to the following file on the staging server:DB2 wc_installdir\schema\db2\wcs.staging.keys.xml
    Oraclewc_installdir\schema\oracle\wcs.staging.keys.xml
  7. Start the WebSphere Commerce server on the staging server.

Feedback