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

View all posts

Leave a Reply

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