Looking for guinea pigs to help test my new tool for Umbraco, details below:

Umbraco Export Tool

Downloads
Executable (unzip and run)
Source
.NET Framework
SQL Express

About
This tool exports your umbraco data from one database to another. The tool is written for people who use shared hosting and don't have the luxury of being able to backup the database when they want, or install SSIS package. The tool replaces the SQL Enterprise Manager/SQL Server Management Studio export (which doesn't copy keys/constraints anyhow) and is written specifically for Umbraco database objects.

The tool assumes you know about connection strings and how to SQL Enterprise Manager/Management Studio. It isn't intended as a replace for Darren Ferguson's backup tool, but just as a one-time backup tool (for moving to new systems/working locally) and has no scheduling facility. You will need to FTP and backup your website filesystem manually.

You can copy straight to your own PC for backup using SQL Express (a free version of SQL Server) available at: http://msdn.microsoft.com/vstudio/express/sql/download/. You will need to install the SQL Server Management Studio Express to run the output scripts if you choose to script out the data (which is recommended for large Umbraco systems). This is available at the link above.

The tool requires the .NET framework 2.0 which is available from http://www.microsoft.com/downloads/details.aspx?FamilyID=0856eacb-4362-4b0d-8edd-aab15c5e04f5&displaylang=en .

It currently only works on Windows, but may compile on Linux/Mac OS X using the Mono framework. If there is enough demand then I will convert it to a web application.

Usage guide
The tool has the following options:

* Source connection string
The source database connection string, usually taken from your live umbracoDbDsn setting

* Destination connection string
The destination database connection string, this will usually be your local database.

* Drop (umbraco) database objects
Drops all Umbraco tables and views from the destination database

* Create umbraco objects
Creates all umbraco tables and views (but not foreign keys) on the destination database

* Save SQL output
Once the database has been taken from the source database, you will be prompted with a file dialog to save the SQL script to if this option is checked. This script also contains the foreign key creations in it. Use this option if you have large amounts of data.

* Execute insert queries
If checked, the data that has been scripted will be run on the destination database (including foreign key definitions).

* Set dateformat
If your source database is a different date format than destination one, for example you host in the US but live in the UK, then set this to the format of your destination dateformat. See the SQL documentation for the format of this, the general idea is dmy = datemonthyear.

Note for large umbraco systems
If your umbraco system has a large amount of pages/data on it, it's recommended that you script out the database and don't use the "Execute insert queries" option. You can then run this script using Enterprise Manager/Management Studio. If you don't do this, the application will spend a long time looking like it's doing nothing and you won't get an indication of the progress. It may also timeout during this. The foreign keys are added to the end of the script, with NOCHECK added so any existing data that breaks the foreign keys doesn't fail.

Known issues
- Sometimes there are issues with pages becoming unpublished (atleast there were in testing). This will probably involve the Umbraco developer team's input to fix as it looks like it's a foreign key issue.
- The interface can sometimes hang up, this will be improved over time.

Please note: it's in 'beta' so don't try it on production systems.

Edit:

I am trying your tool out now.. Pretty slick..

Couple recommendations. Allow the option to not back-up log, stat, and statSession tables (maybe others). I have 11,000, 30,000, and 85,000 respectively. (There may be other unnecessary tables as well)

Beyond that.. Cool Tool..

---

The tool now lives at:

http://www.codeplex.com/UmbracoExportTool

It'd be nice if a link could be placed on the website somewhere so it doesn't get lost here, but not urgent.

Hi, thanks for the bug report. If you install the latest version, this will fix the problem you had.

Hey Chris,

If you want to get into the meat and potatoes of umbraco, an additional functionality, that some would love, would be the ability to truncate out previous versions of documents. It may be a pain to figure out at first, but, it would be well worth it for making a process of migrating dev systems to production.

Just a thought.

Case

---

Horst - thanks for spotting this, as I couldn't work out why the publication dates were being reset.

This is actually quite a serious bug, as Umbraco keeps republishing itself based on these dates expiring. I would not use the tool to generate scripts for a live server until I've fixed this.

This is the reason I was having timeout issues on my installation - the cmsPropertyData table on my install had grown to 500,000 rows, when the site itself is no bigger than 500 pages. cmsDocuments had grown to 106,000 rows too.

Casey - that's an idea that I would definitely be interested in following up, as for me the lack of development/staging to live support in Umbraco makes it hard to have local working copy.

I'd either have to do this via triggers on each table, it would be quite close to how replication works in SQL Server; or have an intelligent script that selects and inserts the new items. I suppose as I'm always working with new rows for the data, I don't have to worry about updates except in the tables that deal with datatypes and so on.

I'll have a think and post an update, the existing bugs will be fixed tommorow I hope.

new version up now

Hi Chris,
works like a charm.

For me it's just perfect now.
If I stumble upon any other inconsistencies I will report.


Thank you for the good work, Horst