Planning a data migration without losing records

A step-by-step approach from inventory to rollback plan.
Moving data from one system to another sounds like a technical task, but most problems come from planning. Records are missed, fields do not match, duplicates multiply or the old system is switched off before anyone checks that the new one is right. A careful sequence prevents most of this. This guide walks through one from inventory to rollback.
Start with an inventory
List every place the data lives. The main database is obvious. Less obvious are spreadsheets, exports, attachments, email folders, shared drives and any other system that reads or writes the same records. For each source, note who owns it, how the data is structured, roughly how many records it holds and how often it changes.
Decide what not to move. Old test data, duplicates and records you are required to keep elsewhere can stay behind. Migrating less is simpler and safer, but keep a record of what you chose to leave and why, and check any retention rules that apply.
Understand the new system
Document the structure the data must fit: required fields, formats, limits on length, allowed values and relationships between records. Differences between the two systems are where problems hide. A date stored as text in the old system, a name field split in two in the new one or a status list that does not match will all need a decision.
Map the fields
Create a mapping table: each field in the source, the field it goes to, and any transformation needed. Include rules for the awkward cases.
- Fields with no equivalent in the new system
- Values that need translating, such as old status codes
- Missing values where the new system requires one
- Duplicate records and which one wins
- Linked records, such as orders that point to customers, and the order they must be loaded in
Have the people who use the data review the mapping. They know what the fields really mean, which is not always what the labels say.
Clean the data first
Fix problems at the source where possible, before the move. Standardise formats, merge obvious duplicates and fill gaps you can fill. Cleaning in the old system means the same fix is not repeated at every test run, and it gives you a single clear version to compare against.
Run test migrations
Never make the first run the real one. Do a test migration into a separate environment using a copy of the data, then examine the result. Expect to find problems; that is what the test is for. Repeat until a run completes with no surprises.
Script the migration so it is repeatable. A scripted process behaves the same every time and can be rehearsed. A manual one introduces fresh mistakes on each attempt.
Verify the result
Define in advance what “correct” means, then check it.
- Record counts match between source and destination, allowing for the records you excluded on purpose
- Totals that should agree, such as balances or quantities, do agree
- A sample of individual records matches field by field
- Linked records still connect to the right parents
- Special characters, dates and number formats survived intact
- Users test real tasks in the new system and sign off
Have someone who did not run the migration do the checking. Fresh eyes find different things.
Plan the cutover
Choose a time when change is least disruptive, and tell everyone who is affected. Decide whether to freeze changes in the old system during the move, or to capture and replay what changes while it runs. A short freeze is usually simpler, as long as the business can tolerate it.
Write the cutover plan as a numbered list with an owner and an expected duration for each step, and rehearse it with the same scripts you will use on the day.
Have a rollback plan
Before you start, decide how you would go back if the move fails or the checks reveal serious problems. Take a full backup of the source and confirm you can restore it. Set a clear point of no return, and agree who has the authority to call a rollback. Keep the old system intact and read-only for an agreed period after launch rather than decommissioning it straight away.
After the move
Watch closely in the first days. Expect questions from users and a few edge cases the tests missed. Keep a log of issues and fixes. When you are confident, archive the old data according to your retention rules, update documentation and close the old system.
A short summary
- Inventory every source and decide what not to move
- Document the target and map every field
- Clean the data before moving it
- Script the migration and rehearse it in a test environment
- Verify with counts, totals, samples and user testing
- Plan the cutover step by step
- Prepare and test a rollback before you begin
Migrations reward patience. The time spent on mapping, testing and verifying is what lets the real run be uneventful, which is exactly what you want.
Have a question or project in mind?
Tell us what you’re trying to achieve and we’ll help you figure out the best way to build, grow or streamline it.

