I spent most of yesterday trying to make an application that will allow you to backup and restore databases in an online application using ASP.net / c#. I found a really nice bit of code thanks to Sheo Narayan's excellent Back and Restore Database in ASP.Net tutorial. However, the code did not preserve the values of the identity field, which is an essential feature of what I needed.

After much research and experimentation, I found that if you change the way the XML file is generated to include the database schema, then identity inserts are not a problem - changing the line:

dataset.WriteXml(tableName + ".xml"));

to

dataset.WriteXml(tableName + ".xml"),XmlWriteMode.WriteSchema);

sorted the problem out for me. I've posted the ASP.net Backup and Restore Database to XML files code in the scripts database.

I then extended the functionality of the system to allow xml file uploads of previously generated database backups, multiple table backups, multiple table restores, backup set histories (so each backup point is given it's own xml file allowing you to select any previous backup set to restore), folder security and authentication, database schema comparison (checks to make sure that the database structure matches that in the xml files) and then wrapped the whole lot up into a neat reusable set of classes. I'll post the code for version 2 of the Backup and Restore database application once it has completed unit testing shortly.