Skip to main content

ESP - Database Copy Script

/*
Search and replace for the ESP database and its folders

Created By	: SFL 08/13/23
Modified By	: SFL 08/21/24

Use at own risk

*/ 
USE ESP_DEV;
/*
select * from infParameter where value like '%/%'
select * from infPlantValue where value like '%/%'
select * from infGroupValue where value like '%/%'
select * from infpersonalValue where value like '%/%'

select * from infParameter where value like '%\%'
select * from infPlantValue where value like '%\%'
select * from infGroupValue where value like '%\%'
select * from infpersonalValue where value like '%\%'
*/
--update infParameter set mainttime=getdate(),userid='CopyDB', value=replace(value,'\PRD\','\DEV\') where value like '%\PRD\%'

update infParameter set mainttime=getdate(),userid='CopyDB', 
	value=replace(value,'\\SFL-APP-001.onling.com\\KiwiplanInterfaces\Prd\','\\SFL-APP-001.onling.com\KiwiplanInterfaces\Dev\') 
	where value like '%\\SFL-APP-001.onling.com\%'

update infmapdataset set userid='CopyDB', mainttime=getdate(),hostuser='remuser', hostpassword='l5oPU0kDgwy7vPj/uUFuqA==' where hostdataset ='fgsc'

update infParameter set mainttime=getdate(),userid='CopyDB', value='SFL-APP-001' where name ='Purge server name'

delete from infMAPDataset where id not in (select mapdatasetID from orgplant)
update infMAPDataset set mainttime=getdate(),userid='CopyDB', hostname='USW2-D-KP-MAP' where hostdataset ='fgsc'
 
update infParameter set mainttime=getdate(), userid='CopyDB', value='TEST 9.80.5040' where name='database status'
 
delete from agtAgentServer where id not in (select agentserverID from agtagent)
update agtAgentServer set mainttime=getdate(),userid='CopyDB', name='SFL-APP-001'

update agtagent set mainttime=getdate(),userid='CopyDB', agentServerID =(select id from agtAgentServer where name='SFL-APP-001')
 
update infParameter set mainttime=getdate(),userid='CopyDB', value='SFL-APP-001' where name = 'KDG server name'

/*
delete from infUserMembership where memberID > (select min(id) from infUser)
delete from infTaskbutton where usernameID not in (select memberid from infUserMembership )
delete from infUser where id > (select min(id) from infUser)
*/

--Need SID search and replace for all users tool ran
 
--create folders that do not exist after replace To DO!

update infparameter set userid='Move_to_', mainttime=getdate() , value=0 where name='Age in Days for archiving' and value != 0
update orgContact set email = ''
update orgAddress set notificationemail = ''

 

Back to top