Reduce the size of your Sitecore dev databases

Just a quick one here and this might not be a problem for most people, but occasionally we like to update our dev environment with a copy of the live Sitecore databases. For alot of our projects these can be huge > 10GB.

One quick way to drastically reduce the size of these databases is to delete all PDF, DOC and DOCX data which is stored within the Blobs table.

This query will do the trick in Sitecore 7 (The schema is different in 6 so you'd have to tweak the query) - please note you will have broken links in Sitecore - but if you can live with that then happy days.

DELETE FROM Blobs where BlobId in
(
    SELECT B.BlobId FROM Items I
    Join SharedFields S on S.ItemId = i.ID 
    Join Blobs B on S.Value = B.BlobId
    Where (I.TemplateID like '0603F166-35B8-469F-8123-E8D87BEDC171'
    or I.TemplateID like '16692733-9A61-45E6-B0D4-4C0C06F8DD3C'
    or I.TemplateID like '7BB0411F-50CD-4C21-AD8F-        1FCDE7C3AFFE')
    and S.FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
)

p.s. It goes without saying but make sure you know what your doing before going renegade with the SQL Deletes!