How to restore a "logically deleted" deactivated record or permanently delete this record in CRM 4.0

Posted on 2011-10-11
Last Modified: 2012-06-27
I've got a question about deleted CRM Records. I allready found out that there is a difference between completely deleted or just logically deleted. When it's only logically deleted I may be able to get the data I need from there.

The situation: An employee created a new record for "Customer B". In a specific field the account number is filled in. Let's say 1176D. At some point she figered out that "Customer B" is actual "Customer A" with only a new company name, but with still the same address and all other details. This "Customer A" has the account number 600D. Knowing this, she deactivated the new created "Customer B" (1176D) and the account number is still available for future use.

Then "Customer C" comes around and must be created in CRM. She creates a new record and gives this customer the next available account number witch is 1176D.

The Problem: The customer uses the CRM database to fill in certain forms in ELO. When searching for account name "Customer A", two account numbers are found (1176D and 600D). When searching for account number 1176D two entries pop up. The wrongly created "Customer B" and the correct "Customer C". Off cause by default the wrong one is the default selection. Since archiving in ELO is automated by this selection, the documents are stored under the wrong Customer Name.

Action taken: The next thing she does is deleting the deactivated record for Customer B, but that does nog solve this issue.

Now what? : The solution could be: Restoring this "deleted" record and remove the account number from the account number field, or permanently delete this "deleted" record.

I'm not to experianced in SQL but I can find my way around in it. Does anybody have an idea how to start with this?
Question by:BGMServices
    LVL 9

    Expert Comment


    When CRM deletes a record it first sets the DeletionStateCode field to 2. This is a flag that states the record is deleted and will be permenantly deleted after some time.

    This way you can set DeletionStateCode back to 0 so you can restore the record from SQL if you have accidentally deleted it.

    As a CRM developer, you should write your queries using this information.

    Like this:

    "SELECT * FROM Account Where DeletionStateCode = 0"
    LVL 29

    Expert Comment

    You discovered that CRM records have an active and inactive state. For accounts, the inactive state is known as deactivated.   Active and inactive records can be searched on and are considered part of the database.

    When a record is deleted, then that record is no longer part of the database and will never appear in searches.

    The actual mechanism for removing records from the database is determined by the CRM application. It is true that deleted records are flagged for later deletion in CRM 4. However, I would warn against on relying on this as a way of retrieving deleted records and it certainly should not form part of any business process. In CRM, modifying records in this way is not supported (though, in this instance it does work). In CRM 2011, records are deleted immediately from the database and so you can't use the deletion state technique to bring records back. In summary, your CRM users should be trained that deleting records is a permanent non-reversible process. You could consider restricting which users are allowed to delete records.

    Author Comment

    Thank you for your comments so fast.

    I never wanted to use this as a fail safe method. The only goal is to make sure that this accountnumber 1176D does not show up two times. Just by logical thinking I figured this out. But...

    if I understand it correctly, I just have to wait to reach my goal. If the faulty record is finaly deleted, my issue is gone too.

    @Sage: Quote: When a record is deleted, then that record is no longer part of the database and will never appear in searches.
    I still do not understand why both account numbers show up by doing a query.

    Is there a way to speed up the permant deletion process? If not, what is the time delay before it is permanently deleted.

    I don't know where to try this query: "SELECT * FROM Account Where DeletionStateCode = 0"
    I do not have much experiance with this.
    Just to know what you are taking about, could you please tell me how and where to do this?

    Thank you.
    LVL 29

    Accepted Solution

    I wonder if your CRM is integrated with another application. You make a reference to ELO which I do not know. If so, perhaps the duplicate account numbers are showing because the CRM tables are being searched directly by the other application. The deleted accounts are present in the CRM table. They get removed on a daily basis by the asynchronous basis.

    Searching on records from within CRM will not return deleted records.

    The SELECT statement mentioned in jkofte's response is a SQL statement which is run in SQL Management Studio. If you are not familiar with SQL Management Studio then be very cautious or get an experienced SQL Administrator to help you.
    LVL 9

    Assisted Solution

    Q: I still do not understand why both account numbers show up by doing a query.
    A: The record is not deleted when you delete it from CRM interface. It is set to be deleted by a flag called DeletionStateCode in SQL table. After some time, CRM permenantly deletes the record.

    Q: Just to know what you are taking about, could you please tell me how and where to do this?
    A: If you are using an external application to reach CRM database, you can change the select queries there. If you are using the Microsoft SQL Server interface, you can just write it on query editor and execute it.

    Make a test: Delete an Account from MS CRM interface, then execute the query I wrote above. You will see that the record is not deleted yet. Check the DeletionStateCode field.

    Author Closing Comment

    My expertise was not good enough to completely understand some of the explanations.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now