Recovering deleted files

If the Purge File Store robot job has not yet run, then it should be possible to recover a file that has been deleted from the File Store.

This cannot be done through Ideagen Internal Audit (Aura) itself, it is necessary to access the data directly from the database.

Files are stored in the database as binary objects, and so the process is:

• Find the file you want to recover
• Convert the binary data into a readable file.  Note that while Ideagen can provide guidance on this second part, we are not able to provide support for the Microsoft tools required

This process has been shown to work with SQL Server 2014, 2016, 2019.

Locate the data

You will normally search for the file by name, and so ideally you should know the name of the file and the parent audit where it is attached.  You will also need to know whether the file is a regular attachment or if it is an audit report.  Example SQL:

For attachments:

-- Attachments
SELECT AttachmentID, ByName, ByTime, FileName, FileUri, ParentAuditName
FROM Attachment_Log
WHERE FileName like '%My Example%'
ORDER BY ParentAuditName, FileName, ByTime;

For audit reports:

-- Audit Reports
SELECT AuditReportID, ByName, ByTime, FileName, FileUri, AuditName
FROM AuditReport_Log
WHERE FileName like '%My Example%'
ORDER BY AuditName, FileName, ByTime;

The query will return all rows for the matching name, ordered by parent audit, name and date saved.  Each time a file is saved a new FileStore row is created and the previous one is deleted, so you are wanting to find the most recent log record for the file you are wanting to recover.

Note the FileURI value. This is the ID that will be used to extract the data.

Extract the data

There are various ways to do this, but one we have used successfully uses Microsoft’s BCP tool.

To use this:

• On your database server, create a new folder that is writable by the tool, e.g. c:\temp
• In this folder, place the attached format file formatfile.fmt (use the version that matches your SQL Server version.  Samples are attached to this article below)
• Use one of the following command sequences, depending on whether your SQL Server has xp_commandshell enabled.  You will need to substitute the database name, File ID (as found above), the file name and path, the SQL Server instance name, the path to the format file:
  
  • If xp_commandshell is enabled, run the following SQL:  

-- IF XP_COMMANDSHELL IS TURNED ON
DECLARE @bcpCommand VARCHAR(2000)
SET @bcpCommand = 'bcp "SELECT FileData FROM PENTANADB.dbo.FileStore WHERE FileID = ''{FileID}''" queryout "c:\temp\YOURFILENAME.XYZ" -T -S SERVER\INSTANCE -f c:\temp\formatfile.fmt'
EXEC master..xp_cmdshell @bcpCommand

• • If xp_commandshell is not enabled:
    • Open a command prompt (you may need to Run As Administrator, depending on permissions)
    • Run this command:

bcp "SELECT FileData FROM PentanaDB.dbo.FileStore WHERE FileID = '{FileID}'" queryout "c:\temp\YOURFILENAME.XYZ" -T -S SERVER\INSTANCE -f c:\temp\formatfile.fmt

Once the command is run, the extracted file will be at the path you specified.

A demo video of this process is available here:  https://app.screendesk.io/recordings/3a6f007d-48c3-4240-9dad-32f40cf35cf4

Recovering corrupt files

Ideagen Internal Audit (Aura) does not maintain a history of saved files, you will not find saved versions of the file prior to the latest one.

If a corrupted file has been saved to the database an you want to recover a prior version, then you will need to:

• Run the relevant query from the first section of this article, to find when the previous version of the file was saved (the ByTime column)
• Get a database backup from a point in time after that save but before the save of the corrupt file
• Restore that database to a server
• Follow the rest of the procedure shown above

Creating a format file

Should it be necessary to create a format file from scratch, follow this procedure at the SQL Server:

• Run this query, in order to create a table containing a sample of the data to be extracted (substituting the file ID):
  

  SELECT FileData
  INTO zRescueFile
  FROM FileStore WHERE FileID = 'FileID';

• Open a command prompt
• Run the following commands, substituting the Pentana database name and server / instance names

  MD c:\temp
  CD c:\temp
  bcp [PentanaDB].dbo.zRescueFile format nul -T -n -S SERVER\INSTANCE -f formatfile.fmt

• You will now have a file called c:\temp\formatfile.fmt
• Open c:\temp\formatfile.fmt in Notepad
• The third line contains an 8 in the third column - swap this for a zero and save - so:
  
  1       SQLBINARY           8       0       ""   1     FileData             ""
  
  becomes
  
  1       SQLBINARY           0       0       ""   1     FileData             ""
  
  
• Save
• the format file is now ready for use

References

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/create-a-format-file-sql-server?view=sql-server-ver16

This procedure references work found at https://stackoverflow.com/questions/27301763/sql-server-bcp-export-corrupted-file