How to simply restore a real company to a newly created test company

Hi,

I believe that you will want to check the "Overwrite the existing database" checkbox under the Options page of the Restore Database window.

And you will want to make sure that you verify the "Restore As" file paths and file names under the options tab.

Also, after you restore, make sure to run the script provided in KB Article 871973.  It will correct old company references throughout the database that can cause GP to behave very strangely.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

/******************************************************************************/
/*	Description:	*/
/*	Updates any table that contains a company ID or database name value	*/
/*	with the appropriate 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, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
	 and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'
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

Select allOpen in new window

"And you will want to make sure that you verify the "Restore As" file paths and file names under the options tab."

Should these reference the current file paths for the live company db or the test db?

Should that script be run against the test company db, or against the system db?

I have to be 100% certain I am not affecting the live company db.

Thank you so much! I couldn't track this down for the life of me

Hi,

Good questions.

The Restore As file paths should point to the Test database that you want to replace / restore into.  I've been meaning to research those fields more to determine if they are really necessary when you do a Restore As, but I always just set them out of habit.

And you should run that script against your Test company after you have completed the restore.

Let me know if you have any other questions.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified Professional

Everything seemed to work well until running the sql script.  it wouldn't run and i got:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKSVC00010'. Cannot insert duplicate key in object 'dbo.SVC00010'.
The statement has been terminated.

when i check that table i have a record for 'TPAI' and for 'TESTX', shouldn't there only be a record for the test company?