EpiServer: Investigating missing blobs using Sql queries

we often need to use existing database and blobs to create new environment. For example, new Dev or staging environment using blobs and database from integration environment

If for some reason blobs are not copied properly then we will have new environment with missing images and it is very hard to workout which blobs are missing and how many blob folders, we supposed to have in blobs folder.

When we hover over on Media assert in Admin view it gives very useful information such as “content id” and “Content Type”. 

Investigating missing blobs using Sql

Now we have media asset’s “content Id” but each blob is stored in Physical directory using “ContentGuid”. The folder names in blobs folder are actually “ContentGuid”s of media assets.

Get Content Type ID

We can run following sql against cms database.

Select pkID from tblContentType 
Name like '%ImageFile%' -- ContentType Name

Get total number of blobs

Now we have ContentTypeID. We can use it to get total number of blobs and blob folders

SELECT count(fkParentID)
FROM [tblContent]
fkContentTypeID = 11 – ContentTypeId

Get total number of blob folders

SELECT count(distinct fkParentID)
FROM [tblContent]
fkContentTypeID = 11 -- ContentTypeId

We can compare number of folders on physical location with numbers from above query.

About the author

Naveed Ul-Haq

I am a UK-based Technical Architect, Founder & Technology Evangelist. I'm Optimizely MVP & Optimizely SME on Content Cloud and Commerce Cloud. I love working on .NET-based CMS, eCommerce solutions, .NET Core, DevOps, and Cloud computing. I'm also a Certified Optimizely Content Cloud Developer, Certified Optimizely Commerce Cloud developer, Optimizely B2B Commerce developer and Microsoft Certified Professional in Azure application development. I spend my free time with my family and reading books. You can contact me at hello@naveedulhaq.com

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *