The recovery script copies a customer’s site back into production by hand-written column lists. The database it copies into has 14,536 columns across hundreds of tables, and it grows every time I ship a feature. Sooner or later the hand-written lists fall behind the schema. So the question I had to answer was simple and unnerving: how do I know my copy lists are still complete?

So I built a 418-line Python tool that parses the ASP file as text, diffs the columns it declares against an exported schema, and tells me exactly which columns the recovery code is silently dropping. The interesting decision wasn’t the parser. It was deciding that the code, not the schema, is the source of truth.

The setup, and why drift is fatal here

The production app is a classic-ASP sports SaaS on Windows/IIS/MSSQL. Each org’s entire site lives as rows keyed by username across hundreds of per-sport tables: <app-db>.<sport>games, <sport>scores, <sport>layoutstruct, and on and on for every sport. There is no RESTORE DATABASE path for “recover this one customer.” Recovery is an application-level copy, row by row, table by table, out of a dated backup database and into live prod.

I had just consolidated a pile of drifting legacy scripts (separate per-sport files for hockey, soccer, baseball) into one 1,477-line master recovery file. It declares each table’s copyable columns through a little DSL:

new SportsTable("links", "category, linkurl, linkname, linkdescription", "priority, catPriority")

Two comma lists per table: string columns first, numeric columns second. That is the contract. Every column the recovery engine knows to copy is sitting in one of those literal strings, hand-maintained. (Some tables, like scores, build their lists in helper functions instead of a literal, but it’s the same idea: a hand-kept column list.)

Here is the failure mode that keeps me up at night. I add a feature that puts a new column on <sport>games. I do not touch the recovery script, because why would I, it’s an internal tool I run twice a year. Six months later a customer’s league gets deleted, I run recovery, and it comes back missing whatever that new column held. No error. No exception. The INSERT just never named that column, so the restored rows quietly carry a default or a NULL. The recovered site looks fine until someone notices a feature’s data is gone.

Discipline does not solve this. “Remember to update the copy lists every time you add a column” works for exactly as long as you are thinking about it, which is never the moment you add the column. I needed the machine to catch the drift.

Export the schema, then diff the code against it

The export side is boring on purpose. One query against sys.tables / sys.columns / sys.types dumps the entire live schema to a CSV. That file is 14,536 rows, one per column. A small helper script fans that single CSV into one file per table under a schema_tables/ directory, so the audit can look up “what columns does <sport>scores actually have” without re-parsing 14k rows every time.

The audit script is the part that earns its keep. It reads the ASP file as plain text and pulls out what the recovery code thinks it should be copying:

  • A hand-rolled brace counter walks the file to grab whole function bodies, the core copy function, the stat-table builder, and the score-string and score-number helpers. There is no AST for Classic-ASP JScript, so you count { and } and slice out the function you want.
  • A regex pulls the column lists out of every new SportsTable("name","a,b,c","1,2,3") it finds.
  • For each table, it diffs the declared columns against that table’s column file from the schema export, and reports two sets: missing (schema has it, the code does not copy it) and extra (the code names a column the schema no longer has).

The output is a flat report with sections for generic tables, the customers table, scores tables, and stat tables. The state you want is every section empty. Empty means the code copies every column the schema has. A non-empty missing list is a precise punch list of exactly which columns recovery is dropping.

A couple of details that mattered in practice:

The per-sport tables all reuse one field list in the code (the same scores columns for basketball, baseball, hockey, etc.), so diffing all of them is redundant and noisy. The tool diffs against one representative sport and trusts that the code applies the same list everywhere.

Identity columns are not copyable and must not show up as “missing.” The audit skips them with a heuristic: ordinal position 1, name ends in ID, integer type, skip it. The primary customer key gets an explicit ignore on top of that. Without these, the report fills with false positives and you stop reading it, which is worse than not having it.

The actual decision: the code is canonical

The thing I want a working developer to take from this is not the brace counter. It’s the source-of-truth call, which I wrote down in the runbook in plain words: source of truth is the code, not the schema. The audit tells you where the schema has more columns than the recovery code is copying.

That is a real fork, and the instinct I keep seeing is to reach for it backwards. The pull is toward “the database is the truth, the code should match it.” But think about what each direction of the diff actually means for this tool:

  • Schema-as-truth would generate the copy lists from the schema. That sounds great until you remember the recovery script is allowed to skip columns on purpose. Identity keys, computed columns, audit timestamps the target should set itself. If the schema is canonical, every one of those is a “discrepancy” you have to suppress, and you are forever maintaining an exclusion list that is just the schema minus your intentions.
  • Code-as-truth treats the hand-written lists as the statement of intent (“these are the columns recovery is responsible for”), and the schema as the thing being checked against that intent. The diff answers exactly one question: has the schema grown columns the code has not been told about yet? That is the only question that causes silent data loss. So that is the question the tool answers, and nothing else.

Picking code-as-truth also matched reality: a human owns those lists, a human is responsible for recovery being complete, and the audit’s job is to make sure that human gets a heads-up when the ground moves under them. The schema is the moving ground. The code is the contract. You diff the moving thing against the contract, not the other way around.

Parsing JScript with regex is a hack, and that is fine

I want to be honest about the unglamorous part. Parsing a programming language with a regex and a brace counter is the kind of thing that gets you yelled at on the internet, usually correctly. It is fragile. Rename a function, change the DSL signature, reformat the file, and the parser can quietly miss a table, which is the exact silent-failure class I built this to kill.

It is still the right-sized tool here, for a specific reason: the target is frozen. The recovery script is Classic-ASP JScript that is never getting a real parser, a language server, or an AST. It changes a few times a year, by me, deliberately. A heavyweight parsing approach would be more code to maintain than the thing it parses. For a stable, hand-edited file with a tiny, regular DSL, a 418-line text parser plus a few guard heuristics is proportionate. The file being frozen justifies the hack, not laziness.

If the ASP file were churning daily, or written by a team, I would not trust the brace counter, and I would want the audit to fail loudly when its own parse looked wrong (zero tables found should be an error, not an empty all-clear). That is the line. The fragility is acceptable exactly because the input does not move much.