Where is the content stored in Umbraco database?

Question

I've inherited a database for an Umbraco website that I need to pull out the content so it can be loaded into another CMS.

I've restored the SQL Server database and I still cannot find where what is essentially 'blog posts' would be stored.

What I'm looking to do is something like:

SELECT * 
FROM blogposts

Thanks

Solution

Unfortunately, it won't be quite that simple. Almost everything in umbraco has representation in the umbracoNode table.

Then, all of the custom property data that you have will be stored in these two tables: cmsPropertyData and cmsPropertyType.

I've found that I can get most of what I want with queries like this:

SELECT TOP 1000 *
FROM cmsPropertyData pd
INNER JOIN cmsPropertyType pt
    ON pt.id = pd.propertytypeid
INNER JOIN umbracoNode n
    ON n.id = pd.contentNodeId
WHERE n.id = 1853

However, you're after info about a specific document type, so you're might look more like this:

SELECT TOP 1000 *
FROM cmsPropertyData pd
INNER JOIN cmsPropertyType pt
    ON pt.id = pd.propertytypeid
INNER JOIN cmsContent c
    ON c.nodeId = pd.contentNodeId
INNER JOIN cmsContentType ct
    ON ct.nodeId = c.contentType
WHERE ct.alias = 'BlogPost'

And then, you'd probably be getting data back for multiple versions of each blog post node, so you'd need to do more joins with the cmsContentVersion table to get just the latest data.

You might think about trying to sidestep some of the database queries and try using the xml cache on disk that already exists. You can find an xml representation of all of the published content at App_Data\umbraco.config. You should be able to trim it down to just the xml representation of the Blog Posts.

I was also reading at this stackoverflow post that you can just export by document type as a Package. You could go to Developer->Packages->Created packages and right click to create. Then in the Package Contents tab, just check the Blog Post document type. Not sure if that will be useful to you because I haven't tried that myself. Looks promising though.

Comments

[Jan Bluemink]

The export package provides an xml file with the relevant data that you can use to import in a CMS from another manufacturer

[Azimuth]

Is umbracoNode.nodeObjectType somehow used? I cannot find any other GUID column in database...

[bowserm]

It isn't just content nodes that are stored in the umbracoNode table. There are also other things like datatypes. You can tell if the umbracoNode record you are interested in is a record for a DocumentType, a content node, or a DataType by looking at that nodeObjectType field.

[Pixel Hunter]

How does it look like for version 8? The expamle does not work anymore.