The short version: Yes, but fake data alone is not enough. Build the screens on made-up records, then run at least one full dry-run load of a copy of your real data into a separate test environment, and only then load for real on cut-over day. I plan two or three trial loads in the first two weeks of a job. Fake data finds design bugs; only real data finds real ones.
Three kinds of data, three jobs
| Stage | Data | What it catches | What it cannot catch |
|---|---|---|---|
| 1. Build | Made-up records, a few dozen | Wrong field types, missing lookups, screens that break on a blank or a long name, logic that fails on an edge case | Anything about your real history |
| 2. Dry run | A copy of the real data, loaded into a test environment | Rows the platform rejects, duplicates, orphans, dates that shift, numbers that round, load time | Nothing about the live environment itself |
| 3. Cut-over | The real data again, into the live environment | Only the last-minute changes since the dry run | Not a place to discover new problems |
The mistake I see is skipping stage 2 because stage 1 went well. Made-up data is clean by construction. Your real data has ten or twenty years of typing habits in it, and the first time you learn that is on the day you switch.
Making fake data that fights back
A hundred neat rows of "Test Customer 1" prove nothing. I load records built to break things, the ones your real data will contain sooner or later:
- A customer with no phone number, no email and no address, and one with three of each.
- Names with apostrophes, hyphens, macrons and a trailing space.
- The longest text you expect, then something twice as long. Dataverse text columns cap at 4,000 characters, as set out in the data types that break table.
- Dates across a daylight-saving change and at midnight, because a date-time column set the wrong way shifts every value by hours.
- A job with no lines, a job with two hundred, and an invoice for zero dollars.
- Two records that look like the same person, to see what the duplicate handling does.
I generate these by hand and with a script, and I keep them. They become the regression pack that gets re-run every time the app or a flow changes, long after go-live.
The dry run, and how to mark it
The dry run loads a copy of the real data into a separate test environment using the same method that will run on cut-over day: a dataflow, Microsoft's Access export, or a scripted import. If the method changes between the dry run and the real load, the dry run proved nothing. Then I check it four ways:
- Row counts per table against the source, all of them, not a sample.
- Totals that the business already trusts: the sum of open invoices, stock on hand, hours booked last month. If these match, the money is right.
- Ten known records looked at by hand, chosen by the person who knows the data, including the strange ones.
- Every rejected row read. A rejected row is usually a real problem that has been hiding in the old system for years, so it gets fixed at the source, not patched in the target.
That is items 3 and 4 of the free 38-point SQL migration checklist. The dry run is repeated until the counts and totals match on a clean pass, which is why it is two or three loads, not one. The time this takes sits inside the bands on how long moving off an old database takes, and it is my scoping estimate, not a measured average.
Keeping real records safe in a test copy
A test environment full of real customer records is still full of real customer records. I keep access to it to the people building and checking, use a separate environment from the live one, and delete it after cut-over. Where the fields do not matter for the test, such as phone numbers and street addresses, I scramble them in the copy first. That is my practice, not a legal requirement I am quoting; if your records carry health or financial detail, ask your own adviser what applies.
Cut-over: the third load
On the day, the old system is frozen read-only from Friday afternoon, the final load runs with the method already proved, counts and totals are reconciled again and someone on your side signs it off before Monday. The old database stays intact and switched off for 60 days. Nothing new should surface, because the dry runs already found it.
When fake data alone is enough
- A brand-new process with no old data. There is nothing to load, so stage 1 is the whole story.
- A small list. A few hundred rows in a spreadsheet can go straight in as a real copy; a fake stage adds nothing.
- A like-for-like copy where the old data is being moved as-is. The dry run does the work; use fake data only for the screens. The method is in moving a desktop database without changing how you work.
Common questions
Should I test a database migration with fake data or real data?
Both, in order. Build and test the screens on made-up records that are deliberately awkward, then run one or more full dry-run loads of a copy of your real data into a separate test environment, and only then load for real on cut-over day. Fake data finds design bugs; only real data finds data bugs.
How many trial loads does a small migration need?
Two or three is what I plan for in the first two weeks of a job, repeated until row counts and trusted totals match on a clean pass. That is my scoping estimate, not a measured average, and it moves with how dirty the source data is.
How do I check the real load matches the old system?
Compare row counts per table, then compare totals the business already trusts, such as open invoices or stock on hand, then check ten known records by hand and read every rejected row. A rejected row is usually a real problem in the old data.
Is it safe to put real customer records in a test environment?
Only if access is limited to the people building and checking, the environment is separate from live, and it is deleted after cut-over. Scrambling fields that do not matter to the test, like phone numbers, is sensible. Check with your own adviser what privacy rules apply to your records.
Not sure what your data will do when it moves?
Tell me the source and roughly how many rows. One free 30-minute call and you get a band, a schedule and a plain view on whether a dry run will earn its keep.