If you rely on synchronisation for remote access then things are a little more complicated than the situation I will describe here; likewise if the deleted record has been recreated by hand. If you are in any doubt, give us a call to discuss your problem in greater detail.

The following discussion assumes that you are confident using SQL Management Studio and are familiar with GoldMine’s internal workings. We will not be held responsible for any mishaps arising from your use of this blog post.

Always make a new backup before doing any surgery like this.

To recover a deleted record, you need to turn to your backups. Using SQL, restore a backup to a different database name (DO NOT overwrite your live database!).

Open a new query in Management Studio and copy in the following:


declare @backup varchar(50)
declare @live varchar(50)
declare @accountno varchar(50)
declare @table varchar(10)

set @backup='restored_db'
set @live='live_db'
set @accountno='A8031147428)2P:R'

set @table='contact1'

exec (
'print ''' + @table + ''''
)

exec (
'DELETE FROM ' + @live + '.dbo.' + @table +'
WHERE recid IN
(SELECT recid FROM ' + @backup + '.dbo.'+ @table +' WHERE accountno=''' + @accountno + ''')'
)

exec (
'INSERT INTO ' + @live + '.dbo.' + @table +'
SELECT * FROM ' + @backup +'.dbo.'+ @table +'
WHERE ' + @backup + '.dbo.'+ @table +'.accountno=''' + @accountno + ''''
)

set @table='contact2'

exec (
'print ''' + @table + ''''
)

exec (
'DELETE FROM ' + @live + '.dbo.' + @table +'
WHERE recid IN
(SELECT recid FROM ' + @backup + '.dbo.'+ @table +' WHERE accountno=''' + @accountno + ''')'
)

exec (
'INSERT INTO ' + @live + '.dbo.' + @table +'
SELECT * FROM ' + @backup +'.dbo.'+ @table +'
WHERE ' + @backup + '.dbo.'+ @table +'.accountno=''' + @accountno + ''''
)

set @table='conthist'

exec (
'print ''' + @table + ''''
)

exec (
'DELETE FROM ' + @live + '.dbo.' + @table +'
WHERE recid IN
(SELECT recid FROM ' + @backup + '.dbo.'+ @table +' WHERE accountno=''' + @accountno + ''')'
)

exec (
'INSERT INTO ' + @live + '.dbo.' + @table +'
SELECT * FROM ' + @backup +'.dbo.'+ @table +'
WHERE ' + @backup + '.dbo.'+ @table +'.accountno=''' + @accountno + ''''
)

set @table='contsupp'

exec (
'print ''' + @table + ''''
)

exec (
'DELETE FROM ' + @live + '.dbo.' + @table +'
WHERE recid IN
(SELECT recid FROM ' + @backup + '.dbo.'+ @table +' WHERE accountno=''' + @accountno + ''')'
)

exec (
'INSERT INTO ' + @live + '.dbo.' + @table +'
SELECT * FROM ' + @backup +'.dbo.'+ @table +'
WHERE ' + @backup + '.dbo.'+ @table +'.accountno=''' + @accountno + ''''
)

set @table='mailbox'

exec (
'print ''' + @table + ''''
)

exec (
'DELETE FROM ' + @live + '.dbo.' + @table +'
WHERE recid IN
(SELECT recid FROM ' + @backup + '.dbo.'+ @table +' WHERE accountno=''' + @accountno + ''')'
)

exec (
'INSERT INTO ' + @live + '.dbo.' + @table +'
SELECT * FROM ' + @backup +'.dbo.'+ @table +'
WHERE ' + @backup + '.dbo.'+ @table +'.accountno=''' + @accountno + ''''
)

set @table='opmgr'

exec (
'print ''' + @table + ''''
)

exec (
'DELETE FROM ' + @live + '.dbo.' + @table +'
WHERE recid IN
(SELECT recid FROM ' + @backup + '.dbo.'+ @table +' WHERE accountno=''' + @accountno + ''')'
)

exec (
'INSERT INTO ' + @live + '.dbo.' + @table +'
SELECT * FROM ' + @backup +'.dbo.'+ @table +'
WHERE ' + @backup + '.dbo.'+ @table +'.accountno=''' + @accountno + ''''
)

set @table='cal'

exec (
'print ''' + @table + ''''
)

exec (
'DELETE FROM ' + @live + '.dbo.' + @table +'
WHERE recid IN
(SELECT recid FROM ' + @backup + '.dbo.'+ @table +' WHERE accountno=''' + @accountno + ''')'
)

exec (
'INSERT INTO ' + @live + '.dbo.' + @table +'
SELECT * FROM ' + @backup +'.dbo.'+ @table +'
WHERE ' + @backup + '.dbo.'+ @table +'.accountno=''' + @accountno + ''''
)


Look for the three ‘set’ commands at the top of the query. Replace ‘my_restored_db’ and ‘goldmine’ with your own database names and use a SQL query on the backup database to find the accountno of the record that you wish to transfer; put that in in place of the demo accountno.

If you are certain that all the parameters are set correctly then execute the query.