Who is this article for?

Users who want to learn why the number of rows in the FileStore table is not a reliable guide to the number of files imported

Access to Internal Audit's File Migrator tool

This article discusses why a simple comparison of a disk-based file store pre-migration and the number of rows in the database FileStore table post-migration is not a reliable guide to how many documents were imported by the File Migrator tool.

1. Understanding Filestore
2. Counting the number of files migrated
3. Further information

1. Understanding FileStore

Users of Ideagen Internal Audit (formerly known as Pentana Audit) are able to store files within the application.  The main service stores files either within a database table, in a local folder in the file system, or in a file share.

The database table FileStore is the default location for a new system, but customers running the application on their own servers often choose to switch to using a folder instead.  This becomes an issue when moving to Ideagen's hosted platform, because our requirement is that files are stored in the database.

During such a migration the File Migrator is used to move attachments from the folder to the database.  It would be logical to assume that after the migration the number of rows in dbo.FileStore would be equivalent to the number of files on disk, but this is not the case:

• [FileStore] is not empty to start with.  The product ships with a number of templates and example files, all stored in the database.  These are normally not extracted into the file system when the disk-based file store is implemented.
• Deleting a file in the application does not delete it from the file system.  When you delete an attachment, all that is removed is the database record linking the file and its parent record.  The file itself remains intact, albeit unavailable from within the application.
• Modifying an attachment results in an additional file being created.  If a user saves a copy of a file, the service saves the new file and updates the attachment record to point to the new file.  The older copy remains in the system.

Taken together, this all means that the number of physical files in the file store is likely to be much higher than the number of attachments currently available within the application.

The File Migrator copies only the current version of each existing attachment and report to the FileStore table, all deleted and copied files are left behind.  It locates current files based on the parent rows inside the database, and reports each successful and failed upload.

2. Counting the number of files migrated

In order to meaningfully compare the number of files imported with the original file store, do the following:

1. Before doing the file migration, count the number of rows in the database store:

   SELECT COUNT(*) FROM FileStore;

2. Do the file migration.
3. At the end of the process, the File Migrator tool will state how many rows were imported.  
4. Re-run the query at step 1, to count the number of rows in the [FileStore] table again.
5. The current count should now be Original count + number of imported files shown by File Migrator

3. Further information

1. Pentana Audit: Changing the location of attachments – Internal Audit (formerly Pentana Audit) (ideagen.com)
2. Pentana Audit: Assessing the volume of deleted files and purging the FileStore – Internal Audit (formerly Pentana Audit) (ideagen.com)