BIOVIA Notebook: All groups lost

Description: The sync service seems to have deleted all users and then added them again a few minutes later. After this all users have lost their group belongings.

Solution: To resolve the issue by restoring the database the SQL scripts used are given below :

Step 1: SQL used to extract data from copy database

select pkeya, ua.userid usera, PKEYB, ub.USERID groupb, 'insert into eln.groupexport values(''' + ua.USERID + ''', ''' + ub.userid + ''');' as insertstatement
from eln.DIRUSERGROUP du
inner join eln.user_details ua on du.PKEYA = ua.pkey
inner join eln.user_details ub on du.PKEYB = ub.pkey
where PKEYB != 89

Step 2: SQL used to import data to production

create table eln.groupexport(usera nvarchar(40), groupb nvarchar(40));
Run insert statements from output of statement in previous comment.
begin tran
insert into eln.DIRUSERGROUP
select ua.PKEY, ub.pkey
from eln.groupexport ge
inner join eln.USER_DETAILS ua on GE.usera=ua.USERID
inner join eln.USER_DETAILS ub on GE.groupb=ub.USERID
where not exists (select pkeya, pkeyb from eln.DIRUSERGROUP where PKEYA=ua.PKEY and pkeyb=ub.pkey)
rollback tran
(rollback is to be able to check before, change to commit tran)