SQL 2005 and Database Refreshes

[Former Member]

We have 2 NW2004S systems both with ABAP+Java. When we copy one database over from one system (A) to another (B), we've noticed that the schema attached to all of the tables in on the B system are now A.

Using the SQL Tools application from SAP, we were able to fix the ABAP tables, but are unsure as to how to convert Java Tables from A back to B. As a result of this database copy, the Java instance (including configtool) won't start.

Re-installing the Java DB instance did correct the DB schema error, and configtool did run as a result, although the Java CI failed to load.

Any suggestions?

[Former Member · Accepted solution]

Hi,

I think that is better to use the sap way to copy the system. Follow this:

https://websmp203.sap-ag.de/~sapidb/011000358700003085942006E.pdf for Java+abap system copy.

I've used it and works well.

Best regards.

[Former Member]

Hi,

I was wondering if you have been able to get your sql server 2005 database refresh with production data because I am currently in the process of refreshing our QA database environment which is similar to your environment. I was wondering if you can share some of your documentation and procedure you taken down to accomplish the refresh. It would be a big help and greatly appreciated for your generous document and advise.

Thanks,

Tob

[Former Member]

Try to use the script below.

The last line is the script is what you need to change with appropriate database IDs. Look for <tags> to replace appropriately.

Also ensure you create the user SAP<NEWDB>DB already in the database. (specifically in Management->Security ... you know what I mean !!??)

++++++++++++++++++++++++++++++++++++

use <NEWDB_SID>

go

if object_id('sp_change_sapuser') is not null

drop procedure sp_change_sapuser

go

create procedure sp_change_sapuser @oldid sysname, @newid sysname

as begin

declare @oldid_uid smallint

declare @newid_uid smallint

declare @object sysname

declare @object_full nvarchar(999)

set @oldid_uid = user_id(@oldid)

set @newid_uid = user_id(@newid)

if @oldid_uid is not null and @newid_uid is not null

begin

declare object_cursor cursor local for

select name

from sysobjects

where

( (xtype='U' and name <> 'dtproperties')

or (xtype='V' and name not in ('syssegments','sysconstraints'))

or (xtype='P' and name not like 'dt_%')

or (xtype='D' and name not like 'DF__dtpropert%')

or (xtype in ('FN','TF','IF'))

) and @oldid_uid = uid

open object_cursor

fetch next from object_cursor into @object

while @@fetch_status=0

begin

set @object_full = user_name(@oldid_uid) + '.' + @object

exec sp_changeobjectowner @object_full, @newid

fetch next from object_cursor into @object

end

end

else

if @oldid_uid is null

begin

print '*** old database user does not exist ***'

end

if @newid_uid is null

begin

print '*** new database user does not exist ***'

end

end

go

exec sp_change_sapuser 'SAP<OLDSID>DB', 'SAP<NEWDB>DB'

go

++++++++++++++++++++++++++++++++++++

[Former Member]

Thanks for the response.We are running dual stack ABAP+Java. This script will work in ABAP environment but the Java connector still have issue connecting to the refresh database.