Moving file storage from database to file system

Lance Keay asked on April 19, 2017 08:53

Is there an updated article for: https://devnet.kentico.com/articles/moving-file-storage-from-database-to-file-system for versions 9 & 10?

FYI - I have a website that has been storing files in the DB, and the DB is getting too large.

Correct Answer

Roman Hutnyk answered on April 19, 2017 14:55

Have you tried to move all (or selected) attachments to the file system with Kentico @ System -> Files -> Attachments?

4 votesVote for this answer Unmark Correct answer

Recent Answers


Roman Hutnyk answered on April 19, 2017 11:05

Have you checked documentation on this topic?

0 votesVote for this answer Mark as a Correct answer

Lance Keay answered on April 19, 2017 14:38 (last edited on April 19, 2017 14:40)

Yes, but the documentation you provided does NOT address my question. I know where the files are. They are in the DB. I want to get them out of the DB and into the filesystem. These are the 4 biggest tables

  • CMS_AttachmentHistory - 800MB
  • CMS_ObjectVersionHistory - 2GB
  • CMS_VersionHistory - 600MB
  • CMS_Attachment - 300MB

We WERE storing the files in the 'Database' with versioning on the media library and attachments, but the DB is getting too large and unweildly, so we've switched the setting to storing the files in the 'file system' and turned versioning off for these two types of objects. https://www.screencast.com/t/MiMie81tP9H Unfortunately, just changing this setting doesn't do anything to the files already in the Database. NEW files are created in the filesystem. Existing files just sit in the DB.

I'm trying the API code from https://devnet.kentico.com/articles/moving-file-storage-from-database-to-file-system, but that just moves the attachments. There are also a bunch of media.file objects in the CMS_ObjectVersionHistory that are NOT present in the filesystem, so I'd like to create files in the media library from the binary field in the CMS_ObjectVersionHistory table.

Here's what one of the media libs looks like: https://www.screencast.com/t/fy9vApHb - you can see the yellow alert symbol showing that the file doesn't exist in the filesystem. However, if I click the green eye symbol, the image loads from the DB just fine. So the file is there, I just need a way to create a copy in the filesystem.

0 votesVote for this answer Mark as a Correct answer

Lance Keay answered on April 19, 2017 15:59

Thanks Roman, I've never used that system tool, it looks to be the thing for precipitating the attachments from the DB into the filesystem. Thanks!

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on April 20, 2017 09:17

Lance, don't forget to mark Roman's answer as correct, helps other contributors to skip over already answered items and rewards the answer-er!

1 votesVote for this answer Mark as a Correct answer

Chetan Sharma answered on April 20, 2017 09:50

Great idea by Trevor. However I also think Roman's answer is not very explicit. Roman if you could make it more elaborate by adding some screen shots so that it can be useful for newbies to find this information easily.

0 votesVote for this answer Mark as a Correct answer

Lance Keay answered on April 20, 2017 10:14

That tool as pointed out by Roman works great for attachments.

I've ended up manually creating SQL to delete entries from the other 3 tables to free up space.

It would be great if there was a tool in Kentico that would automate this process. eg: when you select to store files only in the filesystem, it would trigger a process to copy them all immediately from the db to the filesystem.

Also when you alter the number of versions to keep, having a process go through and clean up old versions would be nice too.

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on April 20, 2017 16:30

In the Settings you can set where you want to store the files, you can have it in Database, In Files, or Both, shouldn't that cover it? It's found in the Settings Module > System > Files > "Store Files in" right on the top.

If you want complete control over files, it may be possible to leverage the Media Providers to do that:

https://www.youtube.com/watch?v=bQ9GpWbQ39w

Basically there is a thing called a "Media Provider" which can determine how files are handled. They have a class for Azure to handle the files and push them up and get them from Azure as shown in this documentation. It may take some digging, but if you can create your own storage provider class, you can use the STorage Provider to customize how files are handled.

Hope those help!

0 votesVote for this answer Mark as a Correct answer

Lance Keay answered on April 20, 2017 16:35

@trevor - Nope. All that setting does is set your preferences for FUTURE files. It doesn't do anything about files currently existing in the DB. Apparently if you try to access the file via the Kentico API or the CMSDesk, it will then be copied to the filesystem. I haven't seen this happen on my dev environment of Kentico 9.

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on April 20, 2017 16:43

yeah it only applies to older files, in my video i mention that for the azure.

But using the System > Attachments, you can handle any attachments and store in the database, files system, etc. If the setting is set to allow both, you should be able to take all that are in one or the other and copy so they are in both.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.