Audit Recovery Tool

Front End Access to Tool:

System Administration | Tools | Configuration | Framework | Restore Audit Data

Keep in mind before using the tool:

• As of Version 1 of the Audit Recovery Tool, it does not restore related table data. It will only restore the data from the table you are currently selecting from. In other words, please restore the data for each table in the correct required order.
• The restore audit functionality is schema specific. You need to be logged in with the correct schema to see that schema's data.
• When the Audit Table Dropdown is set to AudAuditLogs, the Field Dropdown will be set to 'All' and the Operator to 'Contains'.
• The Deleted By textbox refers to the Username of the user who deleted the record.
• When Using the IN Operator in the Operator Dropdown, seperate values by comma. For Example: 1,2,3.

For SysAudit Table Restores:

• When the Audit Table Dropdown is set to one of the SysAudit tables, the Field, Value and Operator dropdowns are based on the old value field in sysAudit.
• The SysAudit Field Dropdown and SysAudit Value Textbox are optional filters that become available if one of the SysAudit tables are selected.
• When filtering on specific columns for a SysAudit record (Field & Value Criteria), it will only bring back one column. This is fine. The script will restore all the related columns as well.

Criteria Available to find Audit Data:

• Table
  • List of tables that you can use to look for Audit Data.
• Audit Table
  • List of audit tables that contains data for the table selected previously.
• Deleted By
  • Refers to the Username of the person that deleted the record(s)
• Deleted Between
  • Refers to the date(s) the data was deleted before/after/between
• Field
  • Filter on the columns from the Table selection
• Operator
  • Filter Options for Value of the Field.
  • Options Available: Equals, Contains, In, Greater Than, Less Than
• Value
  • Filter value for the Field selection
• SysAudit Field
  • Enables once one of the sysAudit tables are selected as the audit table criteria
  • Allows you to filter on the sysAudit columns directly because IDs are not always saved in sysAudit record
• SysAudit Value
  • Enables once one of the sysAudit tables are selected as the audit table criteria
  • Allows you to filter on the sysAudit column value (Selected above) directly because IDs are not always saved in sysAudit record

How to restore data for clients that do not have the Audit Restore Functionality yet:

1. Utilize your local to access the front-end of the sysAudit Functionality. Add the filter criteria as you would enter it on the client site.
2. Open SQL Profiler and get the EXEC created for sysRestoreAuditData script.
3. Make sure you run all the below scripts on the client and that they run successfully:
   • B_00266_99999_fncSplit_NthItem.sql
   • C_02229_99999_sysSelectAuditTablesByTableName.sql
   • C_02237_99999_sysSelectAllTableColumns.sql
   • C_02238_99999_sysRestoreAuditData.sql
   • C_02258_99999_sysSelectAllForeignKeyReferencesByTableName.sql
   • C_02317_99999_sysSelectAllNonAuditTables.sql
4. Utilize the EXEC (Step 2) to generate the selection of the records that will be restored. You can set the @Debug parameter to 1 if you'd like to get the select statement.
5. Once you have confirmed that you are happy with the data displayed from the select, set @IsRestore equals to 1 and run the exec.
6. Fix any issues if occurs (Mostly it will be FK Constraint issues from other tables that might not have the needed data anymore)

Videos