Need help refreshing business data in SQL 2008

Hello all,

We are running 8.11 SP1 and are in the process of installing a new enterprise server for DV and PY. The server is Windows 2008 SP2 and is running SQL 2008. The original enterprise server is Windows 2003 with SQL 2000.

We utilized the JDE UBE to copy data from SQL 2000 DV on original server to SQL 2008 on new server. In the past with SQL 2000, we would use SQL to restore Prod business data as DV business data, and then change system table ownership within SQL (for example, change SQL table owner from PRODDTA to TESTDTA and PRODCTL to TESTCTL). This was made possible by changing a security setting of the SQL server to "Allow modifications to be made directly to the system catalogs".

With SQL 2005 and 2008, the checkbox for "Allow modifications to be made directly to the system catalogs" has been removed. To copy the business data by the UBE takes over 36 hours. To do the SQL copy and restore takes less than 2 hours. Does anybody know how to change the system catalogs in SQL 2005 or 2008?

Thank you very much for you help,
Adam Wolfson

In our scripts, we use the stored procedure sp_changeobjectowner to do the same thing. You should find that you don't need to change that property in order to use it. Just make sure the user is a dbo or something similar.

Hi,

I tried, a few weeks ago, to backup a DV SQL 2000
database and restore it on SQL 2008 as PD. It works OK.
On the other hand, I strongly suggest you to keep
PRODDTA/PRODCTL owners across all your Business Data
(DV, PY and PD). That way, you won't have to deal with
sp_changeobjectowner every time.

Have a nice day

I didn't know you can have the same owners across the DB's.

Thank you!

Yes, you can, but make sure you make the appropriate changes to the data source definitions so that they match up to what you're using in the database.

Might not be the best idea for compliance reasons. If you have a developer generating a table in DV, they would know the password for PRODDTA and could generate the table in production, stepping on data.

No thanks. seperate owners by data source for me.