Data Migration: Switching Business Software Without Data Loss

The software decision gets all the attention. Teams spend months comparing vendors, sitting through demos, and arguing about features. Then the new system is chosen, everyone relaxes, and the part that actually sinks projects shows up: getting fifteen years of data out of the old system and into the new one without losing anything that matters.

Data migration is where "we're switching systems next month" quietly becomes "we're still running both systems eight months later." It rarely fails because the technology is hard. It fails because nobody owned it, nobody tested it, and nobody admitted how messy the old data really was until it was too late. This guide covers how to plan a migration that goes live cleanly, whether you are moving to a new ERP, a CRM, or a custom system built to replace a tangle of spreadsheets.

Why migration is the part that goes wrong

Most people picture migration as copying rows from one database to another. If your old and new systems stored data the same way, that would be true. They never do. The old system has a "customer name" field that someone has been using to store phone numbers for emergencies. It has three different spellings of the same supplier. It has orders with no customer attached because of a bug nobody fixed in 2019.

New software is stricter. It expects an email in the email field and a date in the date field, and it refuses records that break its rules. So migration is not copying, it is translating: reshaping messy real-world data into the clean structure the new system demands. The work is in the mess, and the mess is always bigger than anyone thinks. That is why you start by looking, not moving.

Audit what you actually have before you move anything

Before you plan how to move the data, find out what you are moving. Pull a real export from the old system and look at it, not the tidy version in your head.

• How many records, really? Customers, orders, invoices, products, documents. The counts tell you the scale and surface duplicates.
• How clean is each field? Sort by each column and scan for blanks, obvious junk, and values that clearly belong somewhere else.
• What is actually used? Old systems accumulate fields nobody has touched in years. You do not have to migrate dead data.
• Where does it break the new system's rules? Required fields that are empty, dates in the wrong format, references to things that no longer exist.

This audit is uncomfortable because it exposes how much cleanup is owed. That is the point. Finding it now costs an afternoon; finding it during go-live costs the go-live.

Decide what to migrate, and what to leave behind

The instinct is to bring everything "just in case." Resist it. Every record you migrate is a record you have to clean, map, test, and verify, and most old data earns its keep in an archive, not in the live system.

A useful split: migrate the data the business runs on day to day, and archive the rest. You almost always need open orders, active customers, current stock, and unpaid invoices. You rarely need every closed ticket from six years ago inside the new system; a read-only export or a database backup you can query if a dispute ever arises is enough. Narrowing the scope is the single biggest lever on how fast and how safely a migration goes. Less data in means less that can go wrong at cutover.

Agree this with the people who use the data, not just IT. The finance team will tell you exactly how far back they need invoices for audits. Sales will tell you which contacts are dead. Write the decision down so nobody reopens it halfway through.

Map old fields to new ones

This is the core of the work: a field-by-field map from the old structure to the new one. For every field in the new system, answer where its value comes from. Sometimes it is a straight copy. Often it is a transformation: splitting one "full name" column into first and last, converting a status code into the new system's labels, or merging two old fields into one.

Some fields will have no source, and you have to decide what to put there, a sensible default or nothing. Some old fields will have no home in the new system, which is your cue to confirm you really do not need them. Build this map as a simple spreadsheet, one row per target field, reviewed by someone who understands both systems. It becomes the specification your migration scripts follow and the checklist you verify against afterwards.

Relationships deserve special care. An order points to a customer; an invoice points to an order. If the IDs change during migration, every one of those links has to be rebuilt, or you end up with orphaned records. Decide early whether you are keeping old identifiers or generating new ones, and keep a lookup table either way.

Clean the data where it is cheapest

Every problem the audit found gets fixed somewhere: in the old system before export, in the migration scripts during transfer, or in the new system after load. The right place is usually wherever it is cheapest and most reusable.

Deduplicating customers is often best done in the old system, where staff recognise which "J. Silva Lda" is the real one. Format conversions, dates, phone numbers, status codes, belong in the migration scripts, where they run consistently every time. Leave as little as possible for manual fixing after load, because post-load cleanup is slow, error-prone, and happens while the business is trying to work in the new system. Automate the repeatable cleanup; reserve human judgement for the genuinely ambiguous records.

Test with a dry run before the real cutover

Never migrate production data for the first time on go-live day. Run the whole thing as a rehearsal into a test copy of the new system, with real data, and then check it hard.

Reconcile the numbers first: if the old system has 4,812 active customers and 61,540 invoices, the new one should too. A mismatch means something was dropped or duplicated, and you want to know why now. Then spot-check records by hand, especially the awkward ones: the customer with the apostrophe in their name, the order with twelve line items, the invoice in a foreign currency. Finally, get the people who use the data every day to look. They will spot in five minutes that the totals are off or a status is wrong, because they know what the numbers should say.

Expect the first dry run to find problems. Fix the scripts, run it again, and keep going until a run comes back clean. A migration you have rehearsed three times is a non-event on the day. A migration you have never tested is a gamble with your operations.

Plan the cutover, and a way back

The cutover is the moment you stop using the old system and start using the new one. Plan it like a small operation. Pick a quiet window, a weekend, a slow season, so a freeze on data entry does not cost you. Decide who runs each step and who signs off that it worked.

The sequence matters because data keeps changing until the last minute. A common pattern: freeze changes in the old system, export the final data, run the tested migration, verify the counts, then let people in. The gap between freeze and go-live is downtime, so you want it short and rehearsed, which is exactly what the dry runs give you.

Have a rollback plan. If verification fails badly after cutover, how do you get back to the old system without losing the work done in the meantime? Usually it means keeping the old system available and read-only for a while, and not decommissioning it until the new one has run clean for a few weeks. Keeping both alive briefly is cheap insurance; deleting the old system the day after go-live is not bravery, it is removing your only safety net.

A pre-migration checklist

Before you commit to a go-live date, confirm you have:

• A real export audited for volume, quality, and rule-breaking data.
• An explicit decision on what migrates and what gets archived, agreed with the teams who use it.
• A field-by-field mapping document reviewed by someone who knows both systems.
• A plan for relationships and identifiers so nothing ends up orphaned.
• Cleanup assigned to the cheapest place, automated where it repeats.
• At least one clean dry run, reconciled by the numbers and spot-checked by real users.
• A cutover runbook with named owners, a change freeze, and a rollback path.

Get those right and the migration stops being the scary part of the project. If you are weighing a switch and want a partner who treats the data move as seriously as the software itself, whether that is a new build or modernising a legacy system, talk to us. The software you choose matters, but the migration is what decides whether the first Monday on the new system is calm or a crisis.