10 Replies Latest reply on Oct 20, 2017 8:55 AM by LFletc

    Deleted contact: only partial restore. ..

    dariobavicchi1 Apprentice

      It happened again: the damned red cross makes possible to easily delete a contact in two clicks including all history data.

      This is not however about the red cross (i have already changed my account settings), but about a (big) problem that makes difficult or impossible to restore the full contact data.

      As i have already done succesfully in the past, i used a backup to transfer the contact record to a secondary database.

      This worked flawlessly and i have now a full  copy of the contact in the secondary backup.

      The problems is when i try to transfer back the contact with the same utility (copy record to other database).

      indeed copy works but only 280 records of history data are transferred: the process monitor shows that 800 more records are skipped during transfer.

      Question is simple: why?

      How it's possible to fix this and transfer all contact data back to the working db?

       

      thanks for help!

        • 1. Re: Deleted contact: only partial restore. ..
          Shaul.Bel Apprentice

          Most likely it's because in the conttlog those records are marked as being deleted.

          To over come this I think you have to delete those entries from the conttlog.

          • 2. Re: Deleted contact: only partial restore. ..
            JMessi Apprentice

            I have a sql query to use that will restore the whole record. You have to know the accountno of the deleted record

            RESTORE DELETE RECORD

            This restore is sync aware:

            Create a restore database and Restore the current GM backup to it.

            To run this query,  be on the current live GM and change the accountno to be the restoring record.

            Change the restoring database name (highlight)

            BECAREFUL WHAT VERSION OF GM

             

            declare @accountno varchar(20), @syncstamp varchar (7)

            set @accountno =  'X'   -- replace the X with the accountno for the restoring record

             

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecIDs]') AND type in (N'U'))

            DROP TABLE [dbo].[RecIDs]

             

            CREATE TABLE
            [dbo].[RecIDs]([RecId] [varchar](15) NULL) ON [PRIMARY]

             

             

            insert into recids select recid from GoldMine_Restore.dbo.contact1 where accountno = @accountno

             

            insert into contact1

            select * from GoldMine_Restore.dbo.contact1

            where accountno = @accountno and recid not in (select recid from contact1)

             

            insert into recids select recid from GoldMine_Restore.dbo.contact2
            where accountno = @accountno

             

            insert into contact2

            select * from GoldMine_Restore.dbo.contact2

            where accountno = @accountno and recid not in (select recid from contact2)

             

            insert into recids select recid from GoldMine_Restore.dbo.contgrps
            where accountno = @accountno

             

            insert into contgrps

            select * from GoldMine_Restore.dbo.contgrps

            where accountno = @accountno and recid not in (select recid from contgrps)

             

            insert into recids

            select recid from GoldMine_Restore.dbo.notes
            where accountno = @accountno

             

            insert into notes

            select *
            from GoldMine_Restore.dbo.notes

            where accountno = @accountno and recid not in (select recid from notes)

             

            insert into recids

            select recid from GoldMine_Restore.dbo.conthist
            where accountno = @accountno

             

            insert into conthist

            select *
            from GoldMine_Restore.dbo.conthist

            where accountno = @accountno and recid not in (select recid from conthist)

             

            insert into recids

            select recid from GoldMine_Restore.dbo.contsupp
            where accountno = @accountno

             

            insert into contsupp

            select *
            from GoldMine_Restore.dbo.contsupp

            where accountno = @accountno and recid not in (select recid from contsupp)

             

            insert into recids

            select recid from GoldMine_Restore.dbo.cal
            where accountno = @accountno

             

            insert into cal

            select *
            from GoldMine_Restore.dbo.cal

            where accountno = @accountno

            and recid not in (select recid from cal)

             

            insert into recids

            select recid from GoldMine_Restore.dbo.mailbox
            where accountno = @accountno

             

            insert into mailbox

            select *
            from GoldMine_Restore.dbo.mailbox

            where accountno = @accountno and recid not in (select recid from mailbox)

             

            insert into opmgr

            select *
            from GoldMine_Restore.dbo.opmgr

            where accountno = @accountno and recid not in (select recid from opmgr)

             

            delete from gmtlog where fieldname like 'zzzd%' and frecid in

            (select * from recids)

             

            delete from conttlog where fieldname like 'zzzd%' and frecid in

            (select * from recids)

             

            set @syncstamp = (select max(logstamp) from gmtlog)

             

            update conttlog

            set syncstamp = @syncstamp,

            logstamp = @syncstamp

            where frecid in

            (select * from recids)

             

            update gmtlog

            set syncstamp = @syncstamp,

            logstamp = @syncstamp

            where frecid in

            (select * from recids)

            1 of 1 people found this helpful
            • 3. Re: Deleted contact: only partial restore. ..
              Shaul.Bel Apprentice

              Nice solution Jill.

              • 4. Re: Deleted contact: only partial restore. ..
                Doug Castell Expert

                Nice script.

                One thing to look out for, however, is that when you delete a contact, it can leave history and mailbox items in the database -- it just removes the accountno value from them. 

                 

                As such, I'd add a couple of statements in there that delete any mailbox or conthist items with the @accountno in question before proceeding to refer to which recids are in (or not) either table.

                1 of 1 people found this helpful
                • 5. Re: Deleted contact: only partial restore. ..
                  PJohns Apprentice

                  I may have missed something here. Is this for a single record? How about restore to a database add that database to your GoldMine system and log in to that new database. Create a new dummy record then merge the deleted one into the new dummy record selecting the dummy as primary. After this copy/move the merged record to you live database.

                   

                  That should work and then you can keep the other database for the next time.

                  • 6. Re: Deleted contact: only partial restore. ..
                    JMessi Apprentice

                    This sql query is to restore 1 deleted record.  You would restore a WHOLE GM backup to another database like in my sql. Then you would query that database to find the GM accountno in that database.  You would then go to the production database and change the X at the beginning to so the query will find all the missing parts.

                     

                    If you do want you want merge 2 records into the new database, you could still loose some data because of the tlogs.

                    My query takes care of the tlogs issue.

                    I have done this sql query many times without data still missing.  You can keep the GoldMine_Restore in you system so if another record gets deleted you can restore a backup to it and do the restore of one record at a time.

                    2 of 2 people found this helpful
                    • 7. Re: Deleted contact: only partial restore. ..
                      PJohns Apprentice

                      Great thanks Jill!

                      • 8. Re: Deleted contact: only partial restore. ..
                        LFletc Apprentice

                        --Steps

                        --1 GoldMine database is called "GoldMine"

                        --2 Restore backup to "GoldMine_Backup"

                        --3 Run queries below

                        --4 Backup database as GoldMine_Fixed

                        --5 PROCEED WITH CAUTION BACKUPS OF LIVE AND BACKUPS NEEDED

                        --6 IF YOU SYNC AND A RMETOE HAS SYNCED THE SITUATION IS MORE COMPLEX...

                        --DELETION LOGS WILL NEED TO BE REMOVED FROM REMOTES AS WELL AS SERVER

                        --YOU WILL NEED TO ADD CODE TO MAKE IT SYNC AWARE

                         

                         

                        I have just spotted an error in my script so I have removed it. A new one will follow shortly.

                        1 of 1 people found this helpful
                        • 9. Re: Deleted contact: only partial restore. ..
                          LFletc Apprentice

                          USE [GoldMine]

                          GO

                           

                           

                          /****** Object:  StoredProcedure [dbo].[usp_RestoreGoldMineRecord]    Script Date: 20/10/2017 15:11:12 ******/

                          SET ANSI_NULLS ON

                          GO

                           

                           

                          SET QUOTED_IDENTIFIER ON

                          GO

                           

                           

                           

                           

                          Alter PROCEDURE [dbo].[usp_RestoreGoldMineRecord]

                          @ACCOUNTNO varchar(20),

                          @Syncaware as int

                          AS

                          BEGIN

                           

                           

                          /* COMMENTS

                           

                           

                          ** IMPORTANT Backup the Live system before restoring!!!

                           

                           

                          Live database is hardcoded as [GoldMine].[dbo].

                          Backup database is hardcoded [GoldMine_Backup].[dbo].

                           

                           

                          Sync aware should = 1 for true and 0 for false

                           

                           

                          Will only work on Goldmine 8.5 or higher

                           

                           

                          */

                           

                           

                          Declare @Lastsyncstamp as varchar(7)

                          set @Lastsyncstamp = (select max(logstamp) from [GoldMine].[dbo].[GMTLOG])

                           

                           

                          --Remove deletion logs from CONTTLOG

                          delete from [GoldMine].[dbo].[CONTTLOG] where fieldname = 'zzzdel' and FRECID in

                          (

                          Select RECID from [GoldMine_Backup].[dbo].[CONTACT1] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTACT2] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTHIST] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTSUPP] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTGRPS] where ACCOUNTNO = @ACCOUNTNO

                          )

                           

                           

                          --Update CONTTLOG to flag records for sync change

                          if (@Syncaware = 1)

                          BEGIN

                          Update [GoldMine].[dbo].[CONTTLOG]

                          set

                          syncstamp = @Lastsyncstamp,

                          logstamp = @Lastsyncstamp

                          where FRECID in

                          (

                          Select RECID from [GoldMine_Backup].[dbo].[CONTACT1] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTACT2] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTHIST] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTSUPP] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[CONTGRPS] where ACCOUNTNO = @ACCOUNTNO

                          )

                          END

                          --Remove deletion logs from GMTLOG

                          delete from [GoldMine].[dbo].[GMTLOG] where fieldname = 'zzzdel' and FRECID in

                          (

                          Select RECID from [GoldMine_Backup].[dbo].[CAL] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[NOTES] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[OPMGR]

                          where

                          RECTYPE not in ('O  ','OZL','OZW') and

                          OPID in (Select RECID from [GoldMine_Backup].[dbo].[OPMGR] where ACCOUNTNO = @ACCOUNTNO and  RECTYPE in ('O  ','OZL','OZW'))

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[OPMGR] where ACCOUNTNO = @ACCOUNTNO and  RECTYPE in ('O  ','OZL','OZW')

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[MAILBOX] where ACCOUNTNO = @ACCOUNTNO

                          )

                          --Update GMTLOG to flag records for sync change

                          if (@Syncaware = 1)

                          BEGIN

                          Update [GoldMine].[dbo].[GMTLOG]

                          set

                          syncstamp = @Lastsyncstamp,

                          logstamp = @Lastsyncstamp

                          where FRECID in

                          (

                          Select RECID from [GoldMine_Backup].[dbo].[CAL] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[NOTES] where ACCOUNTNO = @ACCOUNTNO

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[OPMGR]

                          where

                          RECTYPE not in ('O  ','OZL','OZW') and

                          OPID in (Select RECID from [GoldMine_Backup].[dbo].[OPMGR] where ACCOUNTNO = @ACCOUNTNO and  RECTYPE in ('O  ','OZL','OZW'))

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[OPMGR] where ACCOUNTNO = @ACCOUNTNO and RECTYPE in ('O  ','OZL','OZW')

                          union

                          Select RECID from [GoldMine_Backup].[dbo].[MAILBOX] where ACCOUNTNO = @ACCOUNTNO

                          union

                          select recid from [GoldMine_Backup].[dbo].CASEAttachment where caseid in (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO)

                          union

                          select recid from [GoldMine_Backup].[dbo].CASEContactsLink where caseid in (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO)

                          union

                          select recid from [GoldMine_Backup].[dbo].CASEInfoLink  where caseid in (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO)

                          union

                          select recid from [GoldMine_Backup].[dbo].CASETeamLink  where caseid in (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO)

                          union

                          select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO

                          )

                          END

                           

                           

                          --Restore CONTACT1

                          Insert into  [GoldMine].[dbo].[CONTACT1]

                          Select * from  [GoldMine_Backup].[dbo].[CONTACT1]

                          where

                          ACCOUNTNO = @ACCOUNTNO and

                          RECID not in (Select RECID from [GoldMine].[dbo].[CONTACT1])

                           

                          --Restore CONTACT2

                          Insert into [GoldMine].[dbo].[CONTACT2]

                          Select * from [GoldMine_Backup].[dbo].CONTACT2

                          where

                          ACCOUNTNO = @ACCOUNTNO and

                          RECID not in (Select RECID from [GoldMine].[dbo].[CONTACT2])

                           

                          --Restore CONTSUPP

                          Insert into [GoldMine].[dbo].[CONTSUPP]

                          Select * from [GoldMine_Backup].[dbo].[CONTSUPP]

                          where

                          ACCOUNTNO = @ACCOUNTNO and

                          RECID not in (Select RECID from [GoldMine].[dbo].[CONTSUPP])

                           

                          --Restore Conthist

                          Update [GoldMine].[dbo].[CONTHIST]

                          Set  ACCOUNTNO = BH.ACCOUNTNO

                          from [GoldMine].[dbo].[CONTHIST]

                          join [GoldMine_Backup].[dbo].[CONTHIST] BH ON [GoldMine].[dbo].[CONTHIST].RECID = BH.RECID

                          where

                          BH.ACCOUNTNO = @ACCOUNTNO

                           

                          Insert into [GoldMine].[dbo].[CONTHIST]

                          Select * from [GoldMine_Backup].[dbo].[CONTHIST]

                          where

                          ACCOUNTNO = @ACCOUNTNO and

                          RECID not in (Select RECID from [GoldMine].[dbo].[CONTHIST])

                           

                           

                          --Restore CONTGRPS

                          insert into contgrps

                          select * from [GoldMine_Backup].[dbo].[CONTGRPS]

                          where

                          RECID not in (Select RECID from [GoldMine].[dbo].[CONTGRPS]) and

                          ACCOUNTNO = @ACCOUNTNO

                           

                          --Restore Cal

                          Update [GoldMine].[dbo].[CAL]

                          Set  [GoldMine].[dbo].[CAL].ACCOUNTNO  = BC.ACCOUNTNO

                          FROM [GoldMine].[dbo].[CAL]

                          JOIN [GoldMine_BACKUP].[dbo].[CAL] BC ON BC.RECID = [GoldMine].[dbo].[CAL].RECID

                          where

                          BC.ACCOUNTNO = @ACCOUNTNO

                           

                          Insert into [GoldMine].[dbo].[CAL]

                          Select * from [GoldMine_Backup].[dbo].[CAL]

                          where

                          ACCOUNTNO = @ACCOUNTNO and

                          RECID not in (Select RECID from [GoldMine].[dbo].[CAL])

                          --Restore Notes

                           

                          Insert into [GoldMine].[dbo].[NOTES]

                          Select * from [GoldMine_Backup].[dbo].[NOTES]

                          where

                          ACCOUNTNO = @ACCOUNTNO and

                          RECID not in (Select RECID from [GoldMine].[dbo].[NOTES])

                           

                          --Restore Mailbox

                          Update [GoldMine].[dbo].[MAILBOX]

                          Set 

                          MAILBOX.ACCOUNTNO  = BAK.ACCOUNTNO,

                          MAILBOX.USERID  = BAK.USERID,

                          MAILBOX.FOLDER  = BAK.FOLDER,

                          MAILBOX.FOLDER2  = BAK.FOLDER2

                          FROM [GoldMine].[dbo].[MAILBOX]

                          JOIN [GoldMine_Backup].[dbo].[MAILBOX] BAK

                          on [GoldMine].[dbo].[MAILBOX].RECID = BAK.RECID

                          where BAK.ACCOUNTNO = @ACCOUNTNO

                           

                          Insert into [GoldMine].[dbo].[MAILBOX]

                          Select * from [GoldMine_Backup].[dbo].[MAILBOX]

                          where

                          ACCOUNTNO = @ACCOUNTNO and

                          RECID not in (Select RECID from [GoldMine].[dbo].[MAILBOX])

                           

                          --Restore Opmgr

                           

                          insert into [GoldMine].[dbo].[OPMGR]

                          Select * from [GoldMine_Backup].[dbo].[OPMGR]

                          where

                          ACCOUNTNO = @ACCOUNTNO AND

                          RECID not in (Select RECID from [GoldMine].[dbo].[OPMGR])

                           

                           

                          --Restore Cases

                           

                           

                          insert into [GoldMine].[dbo].CASEAttachment

                          select * from [GoldMine_Backup].[dbo].CASEAttachment

                          where

                          CASEID IN (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO) AND

                          RECID NOT IN (select recid from [GoldMine].[dbo].CASEAttachment)

                           

                           

                          insert into [GoldMine].[dbo].CASEContactsLink

                          select * from [GoldMine_Backup].[dbo].CASEContactsLink

                          where

                          CASEID IN (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO) AND

                          RECID NOT IN (select recid from [GoldMine].[dbo].CASEContactsLink )

                           

                           

                          insert into [GoldMine].[dbo].CASEInfoLink

                          select * from [GoldMine_Backup].[dbo].CASEInfoLink

                          where

                          CASEID IN (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO) AND

                          RECID NOT IN (select recid from [GoldMine].[dbo].CASEInfoLink)

                           

                           

                          insert into [GoldMine].[dbo].CASETeamLink

                          select * from [GoldMine_Backup].[dbo].CASETeamLink

                          where

                          CASEID IN (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO) AND

                          RECID NOT IN (select recid from [GoldMine].[dbo].CASETeamLink )

                           

                           

                          insert into [GoldMine].[dbo].CASES

                          select * from [GoldMine_Backup].[dbo].CASES

                          where

                          RECID IN (select recid from [GoldMine_Backup].[dbo].CASES where ACCOUNTNO = @ACCOUNTNO) AND

                          RECID NOT IN (select recid from [GoldMine].[dbo].CASES )

                           

                           

                           

                           

                          END

                           

                           

                           

                           

                           

                           

                          GO

                          • 10. Re: Deleted contact: only partial restore. ..
                            LFletc Apprentice

                            To execute the stored procedure..

                             

                             

                            ***Live database is hardcoded as [GoldMine].[dbo].

                            ***Backup database is hardcoded [GoldMine_Backup].[dbo].

                             

                            USE [GoldMine]

                            GO

                             

                             

                            DECLARE @RC int

                            DECLARE @ACCOUNTNO varchar(20)

                            DECLARE @Syncaware int

                             

                             

                            set @accountno = '97111757695446342Luk'

                            set @Syncaware = 1

                             

                             

                            EXECUTE @RC = [dbo].[usp_RestoreGoldMineRecord]

                               @ACCOUNTNO

                              ,@Syncaware

                            GO