Data Migration Checklist: Moving Data Without Loss

A safe data migration follows a fixed order: inventory what you have, decide what to move, clean it, map it to the new structure, test the migration on copies until the results reconcile, rehearse the cutover, and keep a rollback plan until the new system has proved itself. Most data loss comes from skipping the middle steps. This checklist walks through each stage in the order you should do them.

Before you start: decide the scope

Write down what is moving, from where, to where, and why. A migration might be a database engine change, an upgrade to a new application, a move to the cloud or a merger of two systems. Each has different risks, but all share the same fundamentals.

• List every source: databases, spreadsheets, file shares, and the quiet side systems people rely on.
• Name an owner for each dataset who can say what is correct.
• Decide what will not move. Old, unused data can be archived rather than migrated.
• Check legal retention requirements before deleting anything, including GDPR obligations. This is not legal advice.

Step 1: inventory and profile the data

Profile each table: row counts, empty values, duplicates, odd formats, and values that break the expected pattern. Profiling queries are simple and revealing:

-- how many rows, and how many with no email?
SELECT COUNT(*) AS total_rows,
       SUM(CASE WHEN email IS NULL OR email = '' THEN 1 ELSE 0 END) AS missing_email
FROM customers;

-- duplicates by email
SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Step 2: clean before you move

Deduplicate, standardise formats (dates, phone numbers, country names), fix obvious errors and decide how to treat missing values. Get the data owner to approve rules for anything ambiguous, such as which of two conflicting records wins. Record every decision; you will need it when someone asks later why a record looks different.

Step 3: map source to target

Create a mapping document with one row per field: source table and column, target table and column, any transformation, and what happens to values that do not fit.

Source	Target	Transformation	Notes
cust.full_name	customers.first_name, last_name	Split on first space	Review single-word names by hand
cust.dob (text)	customers.date_of_birth (date)	Parse DD/MM/YYYY	Reject impossible dates, log them
cust.status (1/2/3)	customers.status (text)	Lookup table	Unknown codes go to a review list

Pay particular attention to character encoding, time zones, decimal precision, and keys. If primary keys change, plan how related records and any external references will stay linked.

Step 4: build the migration as a repeatable script

Avoid manual one-off copying. A scripted, repeatable process lets you run it ten times in testing and once for real with the same behaviour. It should log what it did, reject bad rows into a separate table rather than silently dropping them, and be safe to re-run.

Step 5: test on copies and reconcile

Run the migration against a copy of production data into a test environment, and check the result in layers:

1. Counts: rows in source versus rows in target, per table, with rejected rows accounted for.
2. Totals: sums of key numeric fields such as invoice amounts or stock quantities.
3. Relationships: no orphaned records, no broken references.
4. Samples: pick a few real records and compare them field by field, ideally with the people who know the data.
5. Application tests: do reports, searches and workflows give the same answers as the old system?

-- orphan check: order lines with no matching order
SELECT l.id
FROM order_lines l
LEFT JOIN orders o ON o.id = l.order_id
WHERE o.id IS NULL;

Step 6: plan the cutover and the rollback

Decide how new data will be handled during the move. Options include a freeze window where users stop entering data, or a staged approach with a final catch-up run. Whichever you choose, write the cutover plan as a timed list of steps with a named person against each one.

Your rollback plan must be as concrete as your forward plan: the point at which you can still go back, who decides, how long going back takes, and how you handle any data entered in the new system in the meantime. Take a full backup of the source immediately before the cutover and confirm that it restores.

Step 7: go live and validate

• Run the final migration and repeat the reconciliation checks.
• Have business users run a short list of real tasks before declaring success.
• Keep the old system read-only for an agreed period instead of deleting it.
• Monitor errors and user reports closely for the first days.
• Archive the mapping document, scripts, logs and reconciliation results.

Common mistakes

• Treating it as a copy job. The hard part is understanding and mapping the data, not moving it.
• Skipping rehearsals. The first full run should never be the live one.
• No reconciliation. "It looks fine" is not a check; counts and totals are.
• Silent failures. Rows dropped without logging are lost without anyone knowing.
• Ignoring attachments and side data. Files, spreadsheets and integrations tied to the old system need a plan too.
• No rollback. Hope is not a recovery strategy.
• Deleting the old system too early.

Choosing a migration approach

There are two broad approaches, and the right one depends on how long the business can tolerate a pause.

Approach	How it works	Best when	Risk
Big bang	Freeze the old system, migrate everything, switch over once	Data volume is modest and a short outage is acceptable	Everything depends on one window going well
Phased	Move by module, site or dataset over several stages	The system is large or downtime is costly	Two systems must coexist and stay in step
Parallel run	Run old and new together for a period and compare	Accuracy is critical, for example in finance	Double data entry and extra effort

Security and privacy during migration

Migrations often create temporary copies of sensitive data in exports, spreadsheets and test environments, and these are a common source of leaks. Treat every copy as live data.

• Use encrypted transfer and storage for exports.
• Restrict who can access test copies, or mask personal data in them where practical.
• Delete temporary files and test databases when the migration is complete, and record that you did.
• Check that the new system's hosting location and access controls meet your data protection obligations. This is not legal advice, so consult the Data Protection Commission guidance or your adviser where there is doubt.

Communication and roles

Name a migration lead who owns the plan, a data owner for each dataset who signs off the results, and a technical lead who runs the scripts. Tell users early about freeze windows and what will change, and give them a way to report problems straight afterwards. Most migration anxiety comes from surprises, not from the migration itself.

The final go/no-go meeting

Hold a short decision meeting before the live run. Confirm that the last rehearsal reconciled cleanly, that the backup of the source has been taken and verified, that everyone knows their task and timing, and that the rollback point is agreed. If any answer is no, delay. A postponed cutover is an inconvenience; a failed one is a recovery project.

Getting the design right

A migration is much easier when the target database is well designed, because the mapping has somewhere sensible to land. Soch Tech's database and application development service covers building the target database layer and integrating it with your existing software, which is where most migrations succeed or struggle.