Sometimes we get calls from customers that has got an employee that has deleted a bunch of contacts in the database. This will happen from time to time if the customer is not too good at setting restrictions in the database. Usually we recommend they restore SuperOffice from backup when this occurs, but what if the deletion has gone unnoticed for a longer period of time?
Recently I had a case like this and we found an 1 month old backup that I could take a look at. It then occurred to me that I could easily use the MSSQL import/Export tool to help with this challenge.
By using SQL queries I can find all contacts deleted by a certain user in the production database, and find these contacts in the backup and restore them with the import tool. The attached sample queries will gather information from the backup database so they can be imported with MSSQL to the correct database table in the destination database.
*crm7.contact (Find all contacts deleted by a spesific user)
select * from superoffice_backup.crm7.contact where contact_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and
type = 4864 and associate_id = 157)
Finne slettede adresser: 1491
select * from superoffice_backup.crm7.address where owner_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and
type = 4864 and associate_id = 157) and address_id not in (select address_id from superofficecrm7.crm7.address)
*crm7.person
select * from superoffice_backup.crm7.person where contact_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and
type = 4864 and associate_id = 157) and person_id not in (select person_id from superofficecrm7.crm7.person)
*crm7.phone
select * from superoffice_backup.crm7.phone where owner_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and
type = 4864 and associate_id = 157)
and phone_id not in (select phone_id from superofficecrm7.crm7.phone)
*crm7.phone
select * from superoffice_backup.crm7.phone where owner_id in
(
select person_id from superoffice_backup.crm7.person where contact_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and type = 4864 and associate_id = 157))
and phone_id not in (select phone_id from superofficecrm7.crm7.phone
)
*crm7.email
select * from superoffice_backup.crm7.email where contact_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and
type = 4864 and associate_id = 157)
and email_id not in (select email_id from superofficecrm7.crm7.email)
*crm7.email
select * from superoffice_backup.crm7.email where person_id in
(
select person_id from superoffice_backup.crm7.person where contact_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and type = 4864 and associate_id = 157))
and email_id not in (select email_id from superofficecrm7.crm7.email)
*crm7.udcontactsmall
select * from superoffice_backup.crm7.udcontactsmall where udcontactsmall_id in (
select userdef_id from superofficecrm7.crm7.contact where contact_id in
(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and
type = 4864 and associate_id = 157))
and superoffice_backup.crm7.udcontactsmall.udcontactsmall_id not in (select phone_id from superofficecrm7.crm7.phone)
*
crm7.contactinterest
select * from superoffice_backup.crm7.contactinterest
where contact_id in(select record_id from superofficecrm7.crm7.traveltransactionlog where tablenumber = 5 and
type = 4864 and associate_id = 157) and
superoffice_backup.crm7.contactinterest.contactinterest_id not in (select contactinterest_id from superofficecrm7.crm7.contactinterest)