One row in a linking table pointed at an old record, and a customer’s login was landing in the wrong group. The obvious fix was right there: open a SQL console, run an UPDATE, set the column to the right value, move on. Thirty seconds.

We didn’t do that, and you shouldn’t either. The console UPDATE is the most dangerous thing you can do to production, 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. Fat-finger an ID, or forget that two records share a last name, and you’ve created a second bug that’s harder to find than the first.

So we treated the repair like a deploy. 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 for that is a disposable script that runs inside the app’s own database context. It costs about five extra minutes over the console UPDATE, which is cheaper than one wrong write to a customer’s account.

Here’s the pattern we landed on, built around two real repair scripts we 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 applies the change. The default is the safe path, and you have to opt into danger. 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 it 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 we made when we 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 we expect. Does the linking row actually point where we 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 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 what you’ll be debugging next week.

The rule we followed: every value 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 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. Run it dry, eyeball the report, run with commit=1, and if anything blows up mid-flight you run it again. It picks up where it left off instead of double-applying or erroring out. Idempotency turns “we hope this works the first time” into “we 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 step for writing down what we changed, because the change record is a byproduct of running the thing. The dry run produces the proposed diff and 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 instead of anyone’s memory.

Why a script in the app instead of a console

A few specific reasons this beat 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 uses for every query. We weren’t reasoning about whether an ad-hoc console connection had the same collation or 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 any change gets, 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, so the decision to write to production is a reviewed decision.

The timestamp that wouldn’t go back in

While building one of these, we hit something a console would have hidden. We needed to copy a confirmation timestamp from the old record to the new one. Our first pass did the naive thing: we read the old value into the legacy application’s server-side script and tried to write that string straight back.

That was wrong. The script’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. The write-back silently fails or throws, depending on how you wrapped it.

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

We caught it 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 we’d have caught it the way we catch most things in a console session: in production, after the fact.

Two broken logins, two different repairs

That same week we had two logins broken with the identical symptom, each showing the wrong group, and the identical root cause, a stale row in the same linking table. 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.

Had we written one repair script and reused it on both because the symptom matched, we’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 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.

Before the next hand-written UPDATE

Ask for a repair script that defaults to a dry run, asserts each assumption, aborts on the first mismatch, is safe to re-run, and emits a JSON before/after record for the ticket. That’s about five extra minutes, and it leaves a change record someone can review.