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 
Where
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]
where
fkContentTypeID = 11 – ContentTypeId

Get total number of blob folders

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

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

About the author

Naveed Ul-Haq

I'm Naveed. I am a UK based technical architect. I love working with .NET based CMS, eCommerce solutions, .NET Core, DevOps, and Cloud computing. I am a Certified Episerver CMS developer, MCSD (Microsoft Certified Solution Developer) and MCP in Azure application development. I spend my free time with my family and reading books. You can contact me on [email protected]

View all posts

Leave a Reply

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