Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Duplicate company DB to new company??

Status
Not open for further replies.

kpminn

IS-IT--Management
Jan 26, 2010
1
US
Hi,

We have purchased a company running GP Dynamics 8.0. We want to duplicate
the existing company and rename while still maintaining the old one to close out old business.

I have created a new company and when I go to restore it lists the old companies DB. I am logged in to new company. Can I restore from the old co's DB?

I did rename a backup of the old co with the co name but it won't accept that because it refers to a different data base.

Thanks!
 
How are you doing the backup/restore, through GP itself or, SQL Studio?

If you are use SQL studio and can successfully Restore your old company into a new database, you'll want to run the SQL code below, as that updates the DYNAMICS database with reference to the new company database:

Code:
-- Updates any table that contains a company ID or database name value 
use test
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
go 
-- change the database owner
	sp_changedbowner 'DYNSA'
go
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top