Applying data corrections to production through a controlled process
Scripts in git, review, a pipeline that runs them in a transaction with row-count checks and saved output, never by hand from a laptop.
Every DBA knows the situation: a data fix is needed in production, the script is ten lines, and the fastest way is to paste it into a query window from a laptop. That is exactly how a missing WHERE clause ends up in an incident report. I moved data corrections to a controlled process: the script is reviewed in git, a pipeline runs it with the same identity and the same guardrails every time, and the output is kept.
The rule
Nobody runs a correction by hand against production. A correction is a file in a repository. It goes through a pull request, gets approved by someone else, and only the pipeline (or a scheduled runner) executes it.
flowchart LR
A["Script in git (branch)"] --> B["Pull request + review"]
B --> C["Merge to main"]
C --> D["Pipeline: validate"]
D --> E["Manual approval (production)"]
E --> F["Runner: execute in a transaction"]
F --> G{"Row counts as expected?"}
G -->|"yes"| H["COMMIT"]
G -->|"no"| I["ROLLBACK"]
H --> J["Save output as artifact"]
I --> J
J --> K["Report"]
What a correction script looks like
I ask for a fixed shape so a reviewer can read it in one minute: a header with the purpose and the expected number of rows, then the change, which refuses to commit when the count is wrong.
-- Purpose: reset status for rows stuck in 'PENDING' since the import bug
-- Expected rows: 42
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE dbo.Orders
SET Status = 'NEW'
WHERE Status = 'PENDING'
AND ImportBatchId = 1234;
DECLARE @n int = @@ROWCOUNT;
IF @n <> 42
BEGIN
ROLLBACK TRANSACTION;
THROW 50001, 'Unexpected row count', 1;
END
COMMIT TRANSACTION;
Careful: @@ROWCOUNT is reset by almost every statement, so copy it into a variable right after the UPDATE. The idea: the script itself refuses to commit when the count is wrong.
The pipeline
The runner does what the author must not forget to do:
- Validate: the file exists in the expected folder, naming convention respected, encoding fine, no forbidden statement (for example
DROP,TRUNCATE) unless the file is explicitly flagged. - Approve: for production, an environment approval gate in the pipeline.
- Execute with a managed identity or service principal, not a person's account, through
Invoke-Sqlcmd. The runner can also wrap the script in its own transaction when the file does not manage one, with a timeout and a stop on first error (-ErrorAction Stop,SET XACT_ABORT ON). - Capture the full output (messages, rows affected, errors) into a text file and publish it as a pipeline artifact, or upload it where the requester can read it.
- Report: a mail with status, duration and the output file. A failed script never ends silently.
$result = Invoke-Sqlcmd -ServerInstance $server -Database $db -AccessToken $token `
-InputFile $scriptPath -QueryTimeout 600 -Verbose 4>&1
$result | Out-File -FilePath $outputPath -Encoding utf8
Queue or pipeline trigger
Two designs work. In the first, the correction is requested from a ticketing or release tool and lands in a queue; a scheduled runner takes the next script, marks it "running", executes it, uploads the output, marks it "executed". In the second, merging to main triggers the pipeline. I prefer the queue when the requester is not a developer, and the pipeline when the team already lives in git.
Pitfalls
- Status stuck on "running". If the executor crashes between "running" and "executed", the item stays running forever. Decide what a run does with such items, and monitor them.
- Errors that do not throw. Some SQL errors (a constraint violation in a batch) are reported without stopping the PowerShell call. Use
SET XACT_ABORT ON, check the output, and fail the run on any error line. - The script is not validated by the runner. If the only control is the review, the review must be real. Add automated checks for the dangerous patterns rather than hoping.
- Target resolution. The runner resolves the database from an identifier in the request; an unknown identifier must stop the run before anything is executed.
- Large outputs. Cap what goes in the mail; attach the full file and a short excerpt.
Lessons learned
The gain is not speed, it is confidence. Every production change has an author, a reviewer, an approval, an output and a date, and the same script can be rehearsed on a restored copy first. Emergency fixes are still possible, they just go through the same short path.