Manually remove an e-mail from GFI MailArchiver 2011

[Deathlok]

Recently I had the joy of needing to remove an e-mail out of GFI MailArchiver. Which of course as we all know is designed to hold onto e-mails and not be able to easily remove them from without having to dive into the depths of SQL scripts.

The following is how I managed to do it. But I cannot stress highly enough that if you follow these steps, you are doing this
totally at your own risk!
As did I.

This will only work with GFI MailArchiver 2011, I do not think it will work with earlier versions as there are difference in the database tables

First thing is to locate the message itself, to do this I used Outlook Web Access to get to the user`s mailbox and get the relevant details. In this case the subject was distinct enough. But I noted the date and time anyway.
Message was permanently deleted from the inbox. Then went into the MA interface and accessed that users mailbox. Located the message and opened it up.
To find all instances of the message in the SQL database you must look at the Identification code and copy into the clipboard, the number at the end which comes after the last "-" in said identification code. Take the minus character too.

So for instance, you might have something like "-2130511400" and for sake of clarity let`s call it the ***messageID

Please note that this process will remove all traces of that message, so if the message was sent via an e-mail distribution group or to multiple users, the e-mail will vanish for all recipients. You have been warned.

Run the SQL management interface or whatever the equivalent is in the version of SQL server you are running.

From your interface, locate the appropriate database which contains the message. Most set-ups have a rolling quarterly database setup. Luckily mine was in the current one so it was not difficult to figure out. Click on it to ensure it is highlighted and then open up an SQL query window.

Use the following code to find the details of the message... substituting subject-title for what you found previously
select id,recipients,full_subject,receiveddate from arc where full_subject like 'subject-title' order by 'receiveddate'

you can verify you have the correct e-mail from 'receiveddate' and 'full_subject' tables.

Before inserting the code below, I strongly suggest taking the MA services off-line to stop any new messages being added to the live database and then take a manual full backup. If you are contemplating doing this procedure, you should know how to do this!

After the backup is completed... insert the code below into the SQL query window substituting messageID with the number obtained earlier in this article ***.

delete from arc where id = messageID
delete from arc_add where id = messageID
delete from arc_att where id = messageID
delete from arc_bodies where id = messageID
delete from arc_delete where id = messageID
delete from arc_delete_owners where id = messageID
delete from arc_folders where tagid = messageID
delete from arc_hash where id = messageID
delete from arc_keys where msgid = messageID
delete from arc_owners where id = messageID
delete from arc_retention where id = messageID
delete from arc_sources where id = messageID
delete from arc_tags where tagid = messageID
delete from arc_tags_msg where msgid = messageID
delete from arc_tags_msg_deleted where msgid = messageID
delete from arc_tags_msg_updates where msgid = messageID
delete from arc_threads where thread_id = messageID
delete from arc_tx where tx_id = messageID
delete from arc_users where usrId = messageID

Go back to the GFI MailArchiver http interface and refresh the view on the users mailbox. You will see it is no longer there.