Refresh PY with data from PD

Hi, I have an urgent requirement to Refresh PY with data from PD. I found one document E1: ENV: Use R98403 to Copy Data from Pathcode to Pathcode (Doc ID 626170.1). But this document explains to copy from PY to PD. My requirements are to refresh PY from PD. This should also refresh Procurement data in PY i.e. data in all 43 tables that are in use, e.g. F4301, F4311, F43121, etc Could you please guide me in steps how this can be achieved or any documentation recommendation is appreciated. Also appreciate any caution or recommendations.
Thanks
Muhammad

Just Backup production database & restore it in PY and Change the table owners. If you are using CFR 21, disable CFR both in Source and Target Environment just before the refresh.

Eonecnc

R98403 works fine. Just create your own version and fill in the target and source environments properly. Pay close attention to all POs.

And yes, make sure you have a good backup before you run it.

thank you, which one is better, R98403 or backup/restore.
Muhammad

We do this all the time, backup and restore will take time.
What version you are in? I can provide you the script.

Thank you Adeel, we use 812 with 898 TR on windows 2003/sql 2005.

We also do this on an iSeries (AS400). Takes 20-30 minutes. We simply delete the PY data (PY900DTA) and control (CRPCTL) libraries and restore them from backup.

Thanks everyone, can someone please list the steps using the backup method like:
1. Take full backup of JDE_PRODUCTION
2. Restore to JDE_CRP
what other steps are required.
Thanks

Hi,

Basically you will have to run this scripts over your SQL server.

1.- Restore you JDE_PRODUCTION database as JDE_CRP.

2.- If they don't exists yet, add CRPDTA and CRPCTL users to your JDE_CRP database. You can run these scripts anyway.

use JDE_CRP
go
sp_adduser 'CRPDTA','CRPDTA'
go
sp_adduser 'CRPCTL','CRPCTL'
go
sp_adduser 'JDE','JDE'
go


3.- If CRPDTA and CRPCTL users exist, you have to map logins/users.

use JDE_CRP
go
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE','CRPCTL','CRPCTL'
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE','CRPDTA','CRPDTA'
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE','JDE','JDE'
GO


4.- Changing DB Schemas. These queries will generate scripts and you have to run them against JDE_CRP; so execute these queries and execute their outputs.

use JDE_CRP
go
select 'alter schema CRPDTA transfer PRODDTA.' + name from sysobjects where type ='u' and user_name (uid) like '%proddta%'
go
select 'alter schema CRPCTL transfer PRODCTL.' + name from sysobjects where type ='u' and user_name (uid) like '%PRODCTL%'
go

5.- PY is not a production environment, so you can run this scripts to quickly asign permissions.

EXEC sp_addrolemember N'db_owner', N'CRPDTA'
GO
EXEC sp_addrolemember N'db_owner', N'CRPCTL'
GO
EXEC sp_addrolemember N'db_owner', N'JDE'
GO

I hope this help you.

Hi,

The other steps are :

3. Once JDE_CRP database is restored, you'll have
to change ownership of all PRODCTL tables to
CRPCTL and PRODDTA tables to CRPDTA

4. You'll also have to run a GRANT INSERT, UPDATE,
DELETE, SELECT ON xxx.yyyyy TO PUBLIC on all your
CRPCTL and CRPDTA tables

SQL Backup/Restore is zillions of time faster than R98403

Hi Muhammad,

You were not mentioning under which platform are you? As my experiences backup and restore are the best. Only if you are not in AS/400 you need to change the tables owners.

Perfect, thank you everyone.
Muhammad
812/898 on Win 2003

Regarding backup and restore, shoud I restore JDE_PRODUCTION into JDE_CRP or restoration of JDE_PD812 into JDE_PY812 is also required for PY data refresh from PY. Can you please clarify.

Just bring to your attention that

Make sure you PY environment has no ESU which is not deployed in the PD. (Because ESU has table changes and rely on data sometime).


Your PY menu will be overwritten if you have any setup for users for testing.

Thanks
AD

Thank you Adeel,

Shoud I restore JDE_PRODUCTION into JDE_CRP only or restoration of JDE_PD812 into JDE_PY812 is also required for PY data refresh from PY

Yes, you need to do both.

No. You need to restore JDE_Pd812 only if you want to restore central objects.

You mentioned data. So you only need to restore the PROD database. Do not restore the PD812 database because that will bring all of your (central objects)specs from PD to PY. My guess is that you are testing new reports or objects in PY and want fresh data to test with. If you restore the PD812 to PY812 you will overwrite the new reports that you are most likely wanting to test.

Here are the steps we use and I will see if I can attach the procedures we use as well in SQL

Steps to do a spec and data refresh


1. Backup PS_PD811 to PS_PD811*Today’s Date*
a. Set Disk as destination then click Add and select the location to place the backup file
2. Backup PS_PROD to PS_PROD*Today’s Date*
a. Set Disk as destination then click Add and select the location to place the backup file
3. Restore PS_PD811*Today’s Date* to PS_PY811 or PS_DV811
a. Restore from Device and then select the backup file you saved from step 1.
b. In Options, check “Overwrite existing database”
c. Change file locations for .MDF , .NDF and .LDF files to match the locations for the PY or DV environment
4. Restore PS_PROD*Today’s Date* to PS_CRP
a. Restore from Device and then select the backup file you saved from step 1.
b. In Options, check “Overwrite existing database”
c. Change file locations for .MDF and .LDF files to match the locations for the PY or DV environment
5. Run change owner script for PS_PY811 or PS_DV811
a. Open sp_chobjowner script
b. Change database to PS_PY811 or PS_DV811
c. Execute Script
d. Create a new query and enter “chobjowner pd811, py811” substitute dv811 for py811 depending on environment
e. In the results window, select all text that has been created and copy it.
f. Open new query and paste text then execute. Errors on primary key entries are ok
6. Script grant all to public for PS_PY811 or PS_DV811
a. Open SP_grantalltopublic script
b. Change database to PS_PY811 or PS_DV811
c. Execute Script
d. Create a new query and enter “grantalltopublic py811, py811” substitute dv811 for py811 depending on environment
e. In the results window, select all text that has been created and copy it.
f. Open new query and paste text then execute. Errors on primary key entries are ok
7. Run change owner script for PS_CRP or PS_DEV
a. Open sp_chobjowner script
b. Change database to PS_CRP or PS_DEV
c. Execute Script
d. Create a new query and enter “chobjowner proddta, crpdta” substitute testdta for crpdta depending on environment
e. In the results window, select all text that has been created and copy it.
f. Open new query and paste text then execute. Errors on primary key entries are ok
g. Open sp_chobjowner script
h. Change database to PS_CRP or PS_DEV
i. Execute Script
j. Create a new query and enter “chobjowner prodctl, crpctl” substitute testctl for crpctl depending on environment
k. Execute Script
l. In the results window, select all text that has been created and copy it.
m. Open new query and paste text then execute. Errors on primary key entries are ok
8. Script grant all to public for PS_CRPDTA and PS_CRPCTL run script. Script creates procedure which is ran again for example grantallpublic py811, py811
a. Open SP_grantalltopublic script
b. Change database to PS_CRP or PS_DEV
c. Execute Script
d. Create a new query and enter “grantalltopublic crpdta, crpdta” substitute crpdta for testdta depending on environment
e. Execute Script
f. In the results window, select all text that has been created and copy it.
g. Open new query and paste text then execute. Errors on primary key entries are ok
h. Go back to previous query and change to “grantalltopublic crpctl, crpctl” substitute crpdta for testdta depending on environment
i. Execute Script
j. In the results window, select all text that has been created and copy it.
k. Open new query and paste text then execute. Errors on primary key entries are ok
9. On ODEP, Backup PY811 pathcode by renaming it to PY811BAK (substitute PY811 for DV811 depending on environment)
10. On ODEP, Copy PD811 pathcode with all subdirectories except package to PY811 or DV811
11. On ODEP, Copy package subdirectory from PY811BAK to PY811(DV811BAK to DV811)
12. Copy SY811.F9861 table on OENT enterprise server to ol811.F9861*Today’s Date*
a. Open script backup_ol811_F9861
b. Change date in script to current date
c. Execute Script
13. Delete PY811 or DV811 records from original F9861 table
a. Open script Delete_ol811_F9861_DV811 or Delete_ol811_F9861_PY811
b. Execute Script
14. Insert PY811 or DV811 records into SY811.F9861
a. On ODEP log into JDE in the DEP811 environment
b. Go to BV and run R989861
c. Current pathcode=PD811
d. New pathcode=PY811 or DV811
15. Log onto development machine and build a full package and gen specs.

Sql backup to disk. Name with todays date
Restore from disk. Options tab overwrite existing database or force overwrite. Right click tasks, restore, database. Choose source from device add find file I did for backup. Check box restore. Upper left options. Check box that says overwrite existing database. Restore as mdf mlf
Data refresh is less steps. Only deal with PS_DEV or PS_CRP

And here is the other procedure we use