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.

Categorized in: