Recreating lost Umbraco Data

Just had one of the rare occations when Umbraco data does something really unexpected. For some reason my most commonly used document type on a rather big site went bezerk. Almost all property aliases and names was set to empty string, and the datatypes set to the topmost in the list. It must have had something to do with me copying and deleting a (another) datatype. But I was careful doing so.

So – first thought : restore from backup. To be able to do so one obviously need to have a working backup. Do you do backups daily? Really? Check your backups once in a while. Okay?

I had one a day old backup, and I wasn’t so happy about researching through the database for changes from during the day. A better option was to check what had happened in the db with my properties specifically. I looked around a bit and found out that the table cmsPropertyType probably was the one with quirks. Yes most property types for my datatype (identified by id) was nulled out. Still there but Alias and Name was blank.

I could perhaps recreate them manually, but there was some 15 types with names, aliases and descriptions. A fairly easy Update query would have done the trick. But I did not know if there was more errors.

So I gave a database compare tool a shot. Redgate Database Compare. I have had a look at it before, and thought it looked good. Redgate does a good job marketing their products and they seem solid and trustworthy. A bit pricey though. However – they ofcourse know they are helping their customers recreating valuable data.

I bought the product, and made it compare my db against the backed up one. Had the tool generate a Update-script for the one table. It couldn’t run automatically because of some constraint, but with some knowledge about TSql it was easy to pick up the necessary part of the script and run it. Also one feels safer looking at the generated script carefully and not only run it.

After that – and a app pool reset to make Umbraco reread the property types – the site is back to good again. Happiness!

Thomas Höhler wrote a nice blog post about the Umbraco Database structure. And Hendy Racher has another one – with a nice database diagram.

2 thoughts on “Recreating lost Umbraco Data

  1. Whoa, scary that this could happen! I’m glad to have regular backups as well..

    I just wanted to let you know (a bit too late unfortunately) about xSQL, which can do the same data compare that Redgate does, but for free. I’d be curious to see if it produces the same or a very similar update query to what Redgate came up with.

    • Ok, good to know, thanks for the note. I havent looked at xsql yet. Hope I got some extra useability for my bucks, Redgate has a really good UI, and it helped me get some Umbraco db knowledge also, browsing through the tables & diffs. It did produce some extra sql to try to handle the constraints, and still it wasnt successful to run without some tweaks (took out the update parts).

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s