A customer’s login was routing them to the wrong team. One row in a linking table pointed at an old record instead of the current one, and the obvious fix was sitting right there: open a SQL console, run an UPDATE, set the column to the right value, move on. Thirty seconds.

I didn’t do that, and you shouldn’t either. The console UPDATE is the single most dangerous thing a solo dev does to production, precisely because it feels like the cheapest. There’s no dry run. There’s no record of what the row looked like before. There’s no check that the row you’re about to change is the row you think it is. You read a WHERE clause off the top of your head, against live customer data, with no undo. The moment you fat-finger an ID or forget that two records share a last name, you’ve created a second bug that’s harder to find than the first.

The fix is to treat a production data repair with the same rigor as a deploy. Not metaphorically. Literally: dry-run by default, snapshot before and after, assert every assumption, abort on the first mismatch, and make the whole thing idempotent so a half-finished run is recoverable. On a Windows/IIS/MSSQL stack the cleanest place to put that is a disposable script that runs inside the app’s own database context. It costs about five extra minutes over the console UPDATE, and that is cheaper than one wrong write to a customer’s account.

Here’s the pattern I landed on, built around two real repair scripts I wrote in one sitting.

Dry-run by default, commit only on an explicit flag

The first line of the script decides whether anything gets written:

var EDIT_DATABASE = "1" == Clean(Request.QueryString("commit"));

Load the page with no query string and it runs the entire pipeline, builds the full report, and writes nothing. Load it with ?commit=1 and it actually applies the change. The default is the safe path, and you have to opt into danger. That inversion matters: a console session is “live unless you’re careful,” a good repair script is “inert unless you ask.” You can run it ten times while you’re still figuring out the data, and the database never moves.

The dry-run output is not a throwaway either. It’s the exact before/after of every row the script would touch, and that output pastes straight into the ticket as evidence. Anyone reviewing the fix sees what would change before a single byte changes.

A sanity gauntlet that aborts on the first surprise

Before the script writes anything, it re-derives every assumption I made when I wrote it and checks reality against it. Does the person exist. Do both the old and the new records exist. Does the name on the record match what I expect. Does the linking row actually point where I believe it points.

Any mismatch, and the script writes its report and stops:

if (currentLinkedID != EXPECTED_OLD_ID) {
    report.aborted = "row pointed at " + currentLinkedID + ", expected " + EXPECTED_OLD_ID;
    Response.Write(JSON.stringify(report, null, 2));
    Response.End();
}

This is the part the console can’t give you. When you hand-write an UPDATE ... WHERE id = 98765, you are betting that 98765 is still the right row, that nobody touched it since you pulled the ID, that there isn’t a sibling record with the same name two rows down. The script makes that bet explicit and refuses to proceed if it loses. A repair that aborts because reality didn’t match is doing its job. A repair that ran anyway, against a row that drifted out from under your mental model, is the thing you’ll be debugging next week.

The rule of thumb: every value you typed into the script as a literal is an assumption, and every assumption gets an assertion. If you wrote the old record ID by hand, the script confirms the row still carries it before touching it.

Idempotency by detecting already-applied state

Production repairs fail halfway. The connection drops, the page times out, you close the tab. If a re-run errors because step one already happened, you’re now hand-reconstructing partial state under pressure, which is worse than where you started.

So the script detects what it already did. Before re-linking the record, it checks whether the re-link already landed: the old row gone, the new row present, pointing at the right record. If so, it flags that step done and resumes from the next one.

var step1AlreadyDone = (oldLinkRows == 0 && newLinkRows == 1 && newLinkTarget == NEW_ID);
if (step1AlreadyDone) {
    report.steps.push({ step: 1, skipped: true, reason: "re-link already applied" });
} else {
    // perform the re-link, record before/after
}

Now the script is safe to run as many times as you need. First run dry, eyeball the report, run with commit=1, and if anything blows up mid-flight you just run it again. It picks up where it left off instead of double-applying or erroring out. Idempotency is what turns “I hope this works the first time” into “I can run it until it’s done.”

The output is the change record

The script builds a report object as it goes and emits it as formatted JSON:

var report = {
    mode: EDIT_DATABASE ? "COMMIT" : "DRY RUN",
    steps: []
};
// ... each step pushes { before, after, wrote/skipped } ...
Response.Write(JSON.stringify(report, null, 2));

Every touched row gets a before and after snapshot. Every step records whether it wrote or skipped and why. There’s no separate “did I write down what I changed” step because the change record is a byproduct of running the thing. The dry run produces the proposed diff; the commit run produces the actual diff. Both go in the ticket. Six months later when someone asks what happened to this account, the answer is a JSON blob in the comment history, not your memory.

Why a script in the app, not a console

A few specific reasons this beats both the console and a standalone migration tool.

It runs in the app’s real database context. Same connection helper, same include files, same parameterized escaping the application itself uses for every query. You’re not reasoning about whether your ad-hoc console connection has the same collation or the same parameter handling as production. It is production’s code path.

It’s reviewable as a diff. The script is a file in the repo. It goes through whatever review you’d give any change, and it lives in history. A console session leaves nothing behind but a line in a query log nobody reads.

And the dry-run output is the artifact that earns the commit. Before anyone runs it for real, the proposed before/after is sitting in the ticket. The decision to write to production becomes a reviewed decision instead of a reflex.

The gotcha that made me glad I had a script

While I was building one of these, I hit a thing the console would have hidden. I needed to copy a confirmation timestamp from the old record to the new one. My first pass did the naive thing: I read the old value into Classic ASP and tried to write that string straight back. Classic ASP’s Date.toString() produces a string with a timezone abbreviation baked in, and that is exactly the format SQL Server’s date parser refuses to convert back. The write-back silently fails or throws, depending on how you wrapped it.

I treated the timestamp as a string I could safely move through the application. Treating the timestamp that way was wrong: Date.toString() added a timezone abbreviation that the database parser refused, so the write-back could silently fail or throw.

The fix is to never let the value leave the database. Copy it column to column in a single statement with a correlated subquery:

UPDATE records
SET confirmedAt = (SELECT confirmedAt FROM records WHERE id = @oldID)
WHERE id = @newID

I caught this in the dry run, in a report that showed the date round-tripping into a string SQL Server choked on, before any write touched the customer’s account. In a console session I’d have caught it the same way I catch most things in a console session: in production, after the fact.

Same symptom is not the same fix

One more thing the script discipline forced. That same week I had two logins broken with the identical symptom, “I see the wrong team,” and the identical root cause, a stale row in the same linking table. But the data shapes were different. One was a single row pointing at the old record after a record move, and the fix was to re-point it. The other was two rows, a leftover from an old period plus the current one, and the fix was to delete the stale one.

If I’d written one repair script and reused it on both because the symptom matched, I’d have applied re-point logic to the delete case and clobbered the wrong row. Each got its own script with its own assertions checking its own shape. The sanity gauntlet from the second section is what made the difference safe: the second script asserted “two rows, here’s which one is stale” and would have aborted instantly if the first script’s single-row assumption had been wired in by accident. Verify the shape, not the symptom, before you reuse a repair.