Test Environment After Restore Script
Talking to some of the colleagues during the conference, I realized that this script could be beneficial to quite a few. I would love to hear your feedback and moreover contributions if you have something to add. I hope that we can build a quicker way to restore Production into a test and have all of the values reset automatically.
Thank you
-- REM - Final Database cleanup for DB2 UDB for Unix, NT installations.
set log c:\temp\clone_DBNAME.log;
delete from PS.PSDBOWNER;
insert into PS.PSDBOWNER values('DBNAME','SCHEMA');
grant select on PSSTATUS to USER people;
grant select on PSOPRDEFN to USER people;
grant select on PS.PSDBOWNER to USER people;
grant select on PSACCESSPRFL to USER people;
--Invalidate all email addresses
update ps_rolexlatopr
set email_user_sw = 'N';
update PSUSEREMAIL
set emailid = 'xxxxxx@glic.com';
update psoprdefn set emailid = 'xxxxxx@glic.com';
--MASK Vendor Names on Single Pay vendors
UPDATE SCHEMA.PS_VCHR_VNDR_INFO B
SET NAME1 = 'TEST NAME1'
where exists (SELECT 'x'
FROM SCHEMA.PS_VOUCHER
WHERE BUSINESS_UNIT = B.BUSINESS_UNIT
AND VOUCHER_ID = B.VOUCHER_ID
AND VOUCHER_STYLE = 'SGLP'
AND VENDOR_ID LIKE 'SNGL%');
UPDATE SCHEMA.PS_PYCYCL_RST_TBL B
SET NAME1 = 'TEST NAME1'
WHERE EXISTS (SELECT 'X'
FROM SCHEMA.PS_VOUCHER
WHERE BUSINESS_UNIT = B.BUSINESS_UNIT
AND VOUCHER_ID = B.VOUCHER_ID
AND VOUCHER_STYLE = 'SGLP'
AND B.REMIT_VENDOR LIKE 'SNGL%');
UPDATE SCHEMA.PS_PAYMENT_TBL B
SET NAME1 = 'TEST NAME1'
WHERE EXISTS (SELECT 'X'
FROM SCHEMA.PS_PYCYCL_RST_TBL
WHERE BUSINESS_UNIT = B.BUSINESS_UNIT_GL
AND PYMNT_ID = B.PYMNT_ID
AND REMIT_VENDOR LIKE 'SNGL%');
--MASK TIN
UPDATE PS_VENDOR_WTHD SET TIN = '999999999'
WHERE TIN <> '';
-- ********************************************************
-- Purges all entries found in the Process Request tables
-- ********************************************************
-- RUN D:\FSCM851\scripts\prcsclr.dms;
-- ******************************************************************
-- Purges all Application Messaging queue data. Data in both live
-- and archived tables are purged.
-- ******************************************************************
-- RUN D:\FSCM851\scripts\appmsgpurgeall.dms;
-- Update Attachement Servers
-- This needs to have a path to where all of the attachment files will be stored to
-- Usually this would be ftp server directory + environment key
-- User id and password need to have ftp priviledge on PeopleSoft server
UPDATE PS_PV_ATT_SERVERS
SET ATT_FTPSRV_LOGIN = 'user', ATT_FTPSRV_PASSWD = 'password', ATT_SRV_NAME = 'servername',
SERVER_PATH = 'attachment_directory', DEFAULT_SERVER_FLG = 'Y';
-- ******************************************************************
-- Update Attachement Paths
-- Set (e.g. SCM, DEV, PRC,TST) Compare options
-- (this should be NT App Server for the environment )
-- Update (e.g. SCM, DEV, PRC,TST) build index run controls with proper paths
-- ******************************************************************
UPDATE SCHEMA.PS_INSTALLATION_CS
SET CS_VERITY_SRCH = ' ' , CS_CHUNK_SIZE = 20,
APPSERVER_PATH = '/tmp',
CS_NTSERVER_PATH = 'd:\temp', CS_NT_SERVER_URL = 'server:port'
-- ******************************************************************
-- CONFIGURE PUNCHOUT NODES WITH TEST VENDOR URLS
-- UPDATE THIS SECTION IN CASE ADDITIONAL PUNCHOUT/ PO DISPATCH VENDORS ARE ADDED
-- ******************************************************************
UPDATE SCHEMA.PSNODECONPROP
SET PROPVALUE = 'https://punchout url'
WHERE
MSGNODENAME = 'NODENAME'
AND PROPNAME = 'URL';
-- ******************************************************************
-- Update Link Supplier connection properties
-- ******************************************************************
UPDATE SCHEMA.PS_PV_MS_SUPP_NV
SET PV_NV_VALUE = 'value'
WHERE VENDOR_ID = 'vendorid'
AND PV_NV_NAME = 'SHARED_SECRET';
UPDATE SCHEMA.PS_PV_MS_SUPP_NV
SET PV_NV_VALUE = 'value'
WHERE VENDOR_ID = 'vendorid'
AND PV_NV_NAME IN ('SENDER_IDENTITY','FROM_IDENTITY');
-- ******************************************************************
-- CONFIGURE PUNCHOUT ROUTINGS WITH TEST VENDOR URLS
-- UPDATE THIS SECTION IN CASE ADDITIONAL PUNCHOUT/ PO DISPATCH VENDORS ARE ADDED
-- ******************************************************************
UPDATE SCHEMA.PSRTNGDFNCONPRP
SET PROPVALUE = 'https://punchout url'
WHERE
ROUTINGDEFNNAME = 'NODENAME'
AND PROPNAME = 'URL';
UPDATE SCHEMA.PSRTNGDFNCONPRP
SET PROPVALUE = 'https://dispatch url'
WHERE
ROUTINGDEFNNAME = 'NODENAME'
AND PROPNAME = 'URL';
-- ******************************************************************
-- Process Scheduler, Distribution Node changes
-- ******************************************************************
-- If you are using Reverse Proxies
UPDATE SCHEMA.PSWEBPROFNVP
SET PT_PROPVALUE = 'server url'
WHERE WEBPROFILENAME = 'WEB PROFILE' and PROPERTYNAME = 'PSWEBSERVERNAME';
UPDATE SCHEMA.PSWEBPROFNVP
SET PT_PROPVALUE = 'server url:port number'
WHERE WEBPROFILENAME = 'WEBPROFILENAME' and PROPERTYNAME = 'RPS';
-- If you are storing some urls that need to be updated based on the envronment
UPDATE SCHEMA.PSURLDEFN
SET URL =
'https://new url'
WHERE URL_ID = 'URLID';
-- Update report distribution node
UPDATE PS_CDM_DIST_NODE
SET URL='https://SERVER:PORT/PIA_NAME/psreports/ENVNAME',
URI_RESOURCE='SchedulerTransfer/ENVNAME',
uri_rpt='http://servername:port/psc/ENVNAME/EMPLOYEE/ERP/c/CDM_RPT.CDM_RPT.GBL?Page=CDM_RPT_INDEX&Action=U&CDM_ID=',
URI_HOST='servername',
URI_PORT=port
WHERE DISTNODENAME = 'HTTP';
UPDATE PSOPTIONS SET GUID = '';
-- ******************************************************************
-- In case you are using SignOn PeopleCode and need to disable it, e.g. custom single sign on solutions
-- and you need to disable it in test environment to use PeopleSoft security
-- ******************************************************************
UPDATE PSOPTIONS SET OPRID = '';
-- This can vary depending on where the PeopleCode you are using is
UPDATE PSSIGNONPPC SET ENABLED = 'N' WHERE SEQNO = 1;
UPDATE PSSIGNONPPC SET ENABLED = 'Y' ,EXECUTE_AUTHFAIL = 1 WHERE SEQNO = 6;
UPDATE PSSIGNONPPC SET EXECUTE_AUTHFAIL = 0 WHERE SEQNO = 3;