What is your workflow for planning a data migration? So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be migrated". 

It seems like every time it's a one-off, shoot-from-the-hip, best-guess scenario. I feel like this is my weakest skill set as a DBA.

I'd like to get into some patterns for approaching, managing and testing data migrations. 

Please clue me in to some best practices and/or where I can get learning material to help me get better in this area.
Every time I've done it, we've gone for two passes ... 


take a snapshot, and working on a different server, use that to determine what has to be done for the migration, and script it.
once they have the script in hand, the snapshop is restored on the test system, and it's timed to see if it'll run within the required time, or it's tuned and modified until it can.
have the stakeholders sign off that nothing looks wrong with the data on the test system.


Then, over a weekend, you have a scheduled outage :


Friday night, the systems that use the database are brought down, a full cold backup is made, and the scripts are run to migrate/modify/whatever to the data
Systems are brought back up under some private address or somehow set up so it's not open to anyone but the stakeholders for acceptance testing
If the stakeholders approve, the system's put online and made public; if not, the database is restored from the backup made on Friday night, and you start the process over again.


With our schedule, the database folks generally had from 6pm on Friday to 10am on Saturday to run the backup &amp; migration scripts, so our goal was that they'd run in under 8 hrs (~6 of that was backups), so we'd have some time for our testing and corrections before it got released to the stakeholders.

Stakeholders were given their time windows in advance, so they knew to leave their weekend open for testing at the beginning of the window.  They'd also be told the end of their window, typically Sunday afternoon, where if everyone hadn't signed off, we'd have to start rolling back.

Oh, and of course ... if someone had a change during either of the acceptance tests, and we made a change, it meant that all of the stakeholder's sign-offs were voided, and they had to re-test ... so we'd try to give them all a while to look for issues and run any corrections as a batch, rather than apply them one at a time.

Luckily, the only times I've had one of those situations where we couldn't have significant downtime, the sytems I was migrating were fed from scripts, not user input, so I could just have two parallel systems going, and swap them out when things got signed off.  (only once was there a problem, when my boss insisted that we take a full backup, not understanding that the whole thing was going to still be online at a different IP ... so what should've been a 5 min outage on a bad day became a 5 hr outage.)