Updating test company data 1

[clicker666]

We've got two test companies on our GP 7.5 system. The first is World Online and the second is OurCompany Test. My CFO would like the OurCompany Test to reflect the current data in our live company. What is involved in updating the test company to the current data state for the live company?

I would imagine there will be a backup of the day's SQL data tonight, and a restore into the test company, but I'm not quite sure on how to do that. I would imagine the restore tool would just want to try and drop the data into the company it came from, so I will probably need some sort of nifty SQL routine?

 

[jazgeek]

I just do a restore over the test company with my previous days backup.

on the restore, just make sure you check "overwrite" data.



-----------
and they wonder why they call it Great Pains!

jaz

 

[clicker666]

The restore *appeared* to have run, however the database has been saying "Loading" for the past hour and a half. Is this normal?

 

[jazgeek]

depending on your database size it could take up to an hour (mine does at 7 gigs)



-----------
and they wonder why they call it Great Pains!

jaz

 

[clicker666]

I found the problem, I used the "file or filegroups" restore option, as opposed to "device". Once I selected the file through the device options it immediately restored and worked correctly.

 

[bklenzman]

Be careful when doing this. There are several tables that store a value called Company ID. Some modules use this to figure out where (as in to which database) to post a transaction. If you don't update these tables it is possible to have transactions from your TEST company affect the live company. Run this script against your test company:

/*************************************************************
***************************************************/
/*COMPANYID_INTERID.SQL*/
/* Description:*/
/* Updates any table that contains a company ID or database name value*/
/* with the proper values as they are stored in the DYNAMICS.dbo.SY01500 table */
/* */ /***********************************************************
*****************************************************/
if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
where
UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
and b.INTERID = db_name()
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
Exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
select [tableName] as 'Tables that were Updated' from ##updatedTables

 

[clicker666]

Thanks! Some tables were updated, so hopefully nothing bad happened in the interim.