We have all had to do it at some point… but it can get really tricky when you have a project that is constantly changing, especially when data is involved. We have a project where all the data needs to be remain intact, unfortunately these changes were not easy to script.
To migrate the live data into the new structure we used the Entity Framework to generate our old domain from the old database and the new domain from the new database structure. They were imported on different namespaces to determine which model was being dealt with. It was a doddle because it meant mapping the old domain object against the new domain object, then inserting the live data into the new structure.
Saved a lot of time and we weren’t writing SQL queries. What I also liked about this, is the project uses Fluent Nhibernate!