Who is this article for?

Users and database administrators looking for additional help with exporting data from the log table.

Database access is required.

Internal Audit has no 'undelete' function, so in the event a record is deleted, there is no simple way to reinstate it. If logging is enabled, it may be possible to locate and extract useful data from the log tables in the database, in order to facilitate reentering of the information.

This article shows a worked example for extracting deleted Sample Results to a CSV file.

1. Method

This example assumes that:

• We know the name of the Parent Audit and Objective.
• Parent Sample Items have not been deleted.

Sample Results are child data of Sample Items and Tests, as shown by the diagram below:

In this case, the aim is to retrieve information from the Sample Result log table, so if we know the Audit name and Objective name then we can locate the relevant Parent Tests and Samples.

Due to the way that the application logs, we do not have Parent IDs in this log table, only names.  So, we need to find the Parent Sample Item name and Parent Parent Test name and use those to query the Sample Result log.

This works well if deleted data has not yet been reentered. If it has, the user needs to be aware of this and be careful to not duplicate any entries when rekeying.

The extract contains a column called ByTime, which shows the date that a record was last updated, and that indicates the order in which updates were made.

The steps are as follows:

1. Run this query to locate the data.
   You will need to substitute in the Audit name and Objective name in the first two lines:

-- Edit this to use the Parent Audit name
DECLARE @AuditName NVARCHAR(555) = 'Audit Name';               

-- Edit this to use the Parent Objective name
DECLARE @ObjectiveName NVARCHAR(555) = 'Objective Name';       

-- Pull the data from SampleResult_Log

SELECT ID, ChangeCue, ByName, ByTime, TestName, SampleItemName, ResultName, 
  ResultDescription_Plain
FROM SampleResult_Log
WHERE SampleItemName IN (SELECT SI.Name
                            FROM SampleItem AS SI
                                INNER JOIN Sample AS S
                                    ON SI.SampleID = S.ID
                                INNER JOIN Objective AS O
                                    ON S.ObjectiveID = O.ID
                                INNER JOIN Audit AS A
                                    ON O.AuditID = A.ID
                            WHERE A.Name = @AuditName
                                AND O.Name = @ObjectiveName
                                )
AND TestName IN (SELECT T.Name
                    FROM Test AS T
                        INNER JOIN Objective AS O
                            ON T.ObjectiveID = O.ID
                        INNER JOIN Audit AS A
                            ON O.AuditID = A.ID
                    WHERE A.Name = @AuditName
                        AND O.Name = @ObjectiveName
                    )
ORDER BY TestName, SampleItemName, ByTime;

-- Shows the activity by Parent record.  Sort by ByTime to see what happened when.

2. Export the results to CSV (in the SSMS results pane).
3. Sort and filter within Excel to locate the data that was deleted.
4. Reenter the data via the desktop application.

We do not support making changes to data other than via the application UI, and inserting or updating records directly in the database would render your system unsupportable.