Resetting a QA environment between test runs

Write the starting state down as a manifest

Take a hypothetical subscription product, Larkspur Billing, and a QA scenario for its renewal reminder. The scenario needs an active account with a card on file and a renewal ten days out. One tester runs it at nine, gets the reminder, and completes the renewal to finish the scenario. A colleague runs the same steps at ten against that renewed account and files a bug for a reminder that correctly never arrived.

Before choosing how to reset, write down what the environment has to look like when a tester starts. A short manifest kept in version control beside the dataset works, provided every field is something a script can read back from the running environment:

scenario: renewal-reminder
build: larkspur-api 4.18.2, commit 9c41e7a
schema_version: 20260914083012_AddRenewalWindow   # newest row in __EFMigrationsHistory
dataset: qa-renewals r14, captured 2026-03-03T06:00Z
row_counts: {accounts: 212, subscriptions: 187, invoices: 1904}
config: appsettings.QA.json at commit 9c41e7a
payments: Stripe sandbox, new test clock per run
email: QA mail catcher only
dates: shifted to load time

Petrichor's provisioning tool builds a stack in a fixed order: servers with the operating systems the stack needs, then the software, the environment configuration and the customer's application, and last the base users and dataset. Testers log in after that. The manifest check belongs between those two steps: after the dataset loads, before the first tester signs in.

Four reset methods side by side

Each method returns some state to the manifest and leaves the rest where the last run put it.

A rebuild installs the operating system and software from the configuration on every run, so nothing on the servers carries over from the run before. On our platform a QA stack has a shorter life than a dev stack, because each validation run needs a fresh copy loaded with one specific dataset. For one customer, our engineers built three saved environment configurations that the customer's QA lead approved, and the lead knows what each one builds. Petrichor's provisioning tool builds each QA stack from one of those configurations, the stack comes down when its run ends, and its baseline test results stay on file as history. Underneath, the builds run on Salt, Ansible and Terraform, the automation most shops use, and an approved configuration holds each rebuild to the same specification as the one before it.

A VM snapshot resets the whole disk, which makes it tempting for Windows application servers. Two things outside the disk keep moving. A domain member changes its machine account password every 30 days by default, so a server reverted to a snapshot older than its recent password changes can offer the domain a password it no longer accepts, and logins fail with a trust relationship error. Signed in with a local administrator account, run Test-ComputerSecureChannel -Repair -Credential LARKSPUR\qa-join, passing a domain account allowed to reset that computer account. The cmdlet ships with Windows PowerShell 5.1. The guest clock can also come back at the snapshot's time until time synchronization catches up, and the default domain Kerberos policy tolerates five minutes of skew. For servers that must stay joined to a domain, a rebuild, or an image that joins the domain at first boot, avoids both problems.

Cleaning in place is the fastest method and the easiest to get subtly wrong. SQL Server refuses TRUNCATE TABLE on any table a foreign key references, so scripts fall back to DELETE, which leaves the identity counter where the last run left it. Tests that expect the first seeded account to have ID 1 start failing after the first reset. Reseed explicitly. On SQL Server, DBCC CHECKIDENT ('dbo.Accounts', RESEED, 0) after a DELETE gives the next insert ID 1. PostgreSQL clears the tables and resets their sequences in one statement: TRUNCATE accounts, subscriptions RESTART IDENTITY CASCADE.

Rolling back a transaction per test works when the test and the application share one database connection, which is how many frameworks run unit and integration tests. A browser test goes through the deployed application instead, and the application writes through its own connection pool, out of the test's reach. Playwright gives each test a fresh browser context with its own cookies, local storage and session storage, while the server's database stays shared by every test in the run. On a shared environment, give each browser test its own records, such as an account named with the run ID, and reset the whole environment between runs.

SQL Server, PostgreSQL and MySQL commands

SQL Server restore. Stop the application service first. SET SINGLE_USER WITH ROLLBACK IMMEDIATE disconnects every other session, and afterward the database admits one connection, which a running application pool can claim before the restore does.

-- From master, with the application service stopped
ALTER DATABASE Larkspur SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE Larkspur
    FROM DISK = N'E:\baselines\larkspur_qa_r14.bak'
    WITH REPLACE, RECOVERY;
ALTER DATABASE Larkspur SET MULTI_USER;

Restoring the baseline onto a newly built SQL server adds a second failure. Database users map to server logins by SID, and a login created on the new server gets a new SID, so the application's database user comes back orphaned and the application cannot sign in. This query lists orphaned users:

SELECT dp.name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL AND dp.authentication_type_desc = 'INSTANCE';

ALTER USER larkspur_app WITH LOGIN = larkspur_app; remaps one user. Old runbooks still call sp_change_users_login, which is deprecated. The lasting fix belongs in the configuration: create the login with the baseline's SID (CREATE LOGIN larkspur_app WITH PASSWORD = '...', SID = 0x...), and every rebuild matches.

SQL Server Agent stores its jobs in msdb, outside the database you restored. A nightly renewal job on the QA instance keeps its schedule through every reset and runs against the fresh baseline at 02:00 whether a test needs it or not. Have the restore script disable every job that touches QA data, and turn each one back on only for the scenarios that test it.

SQL Server database snapshot. Microsoft's documentation lists managing a test database among the uses for a database snapshot, and reverting to one is faster than restoring a backup.

-- One ON entry per data file in the source database
CREATE DATABASE Larkspur_r14_snap
    ON (NAME = Larkspur_Data, FILENAME = N'E:\snap\Larkspur_Data.ss')
    AS SNAPSHOT OF Larkspur;

-- After the run
RESTORE DATABASE Larkspur FROM DATABASE_SNAPSHOT = 'Larkspur_r14_snap';

Snapshots work at the page level: before a page changes for the first time, SQL Server copies the original into a sparse file. A run that rewrites 5 percent of a 400 GB database's pages grows the snapshot by about 20 GB, and touching 5 percent of the rows can rewrite far more pages than that when those rows are scattered and their indexes change with them. Four limits shape a QA script:

• Reverting requires exactly one snapshot on the database, so drop older ones first.
• While any snapshot exists, the source database cannot be restored from backup or dropped, so a script that mixes the two methods drops the snapshot before it restores.
• A revert rebuilds the transaction log, breaks the log backup chain, and drops full-text catalogs. Databases with FILESTREAM filegroups cannot be reverted at all.
• If the snapshot's disk fills during a run, SQL Server marks the snapshot suspect and it has to be dropped, which leaves nothing to revert to. Size the snapshot volume for the pages a run rewrites.

PostgreSQL. Keep the baseline as its own database and copy it for each run with CREATE DATABASE ... TEMPLATE. The copy fails if any other session is connected to the template, so lock the baseline against connections:

ALTER DATABASE larkspur_r14 WITH IS_TEMPLATE true ALLOW_CONNECTIONS false;
DROP DATABASE IF EXISTS larkspur_qa WITH (FORCE);
CREATE DATABASE larkspur_qa TEMPLATE larkspur_r14 STRATEGY = FILE_COPY;

WITH (FORCE), added in PostgreSQL 13, terminates sessions on the old QA copy, and it still refuses when prepared transactions, active logical replication slots or subscriptions exist. Since PostgreSQL 15, the default copy strategy writes every block to the WAL, which is efficient for a small template. For a baseline of many gigabytes, STRATEGY = FILE_COPY writes far less WAL, at the cost of a checkpoint before and after the copy.

MySQL. MySQL has no template databases, and reloading a mysqldump file replays the dump as one stream of SQL statements, which is slow on anything large. The dump and load utilities in MySQL Shell are faster: util.dumpSchemas() writes the baseline once, and util.loadDump() loads tables in parallel, four threads by default, with deferTableIndexes: "all" building secondary indexes after the data is in. util.loadDump() also records progress in a state file, so an interrupted load resumes where it stopped. On an instance built only to receive the load, ALTER INSTANCE DISABLE INNODB REDO_LOG (MySQL 8.0.21 and later) speeds it up further. MySQL's documentation limits that to loading a new instance and warns that an unexpected stop while redo logging is off can corrupt it, so turn redo logging back on before testers connect, and rebuild the instance if it crashes during the load.

What a database restore misses

After the nine o'clock run, the QA bucket holds a renewal invoice PDF, a reminder email sits in the queue, and Redis keeps keys for account IDs the restore is about to reassign.

Payment sandboxes need a load step of their own. Stripe's test clocks simulate time for billing objects, and a clock only moves forward, so a renewal scenario that runs twice needs a new clock each time. A clock holds at most three customers and advances up to two billing intervals at a time, which is two months for a monthly price. Deleting the clock removes its customers and their subscriptions, and Stripe deletes every simulation on its own 30 days after it was created, so a baseline that stores last month's clock customer IDs points at objects that no longer exist. Create the clock and its customers during the load, write their IDs into the restored database, and delete the clock at the next reset.

When the dataset's dates go stale

The Larkspur baseline was captured on March 3 with an account renewing on March 13, ten days out. Loaded on April 20, the same account is 38 days past its renewal date. If the product suspends accounts 30 days after a missed renewal, the tester lands on a suspended account, and the reminder test fails for a reason unrelated to the build.

Shift scenario dates at load time by the age of the baseline, as the last step before testers log in:

-- SQL Server
DECLARE @shift int = DATEDIFF(day, '2026-03-03', CAST(SYSUTCDATETIME() AS date));
UPDATE dbo.Subscriptions SET RenewsOn = DATEADD(day, @shift, RenewsOn);
UPDATE dbo.Invoices SET IssuedOn = DATEADD(day, @shift, IssuedOn),
                        DueOn    = DATEADD(day, @shift, DueOn);

-- PostgreSQL
UPDATE subscriptions SET renews_on = renews_on + (current_date - date '2026-03-03');

The other approach sits in the application: a clock the QA configuration can pin to one fixed date for every run. Pinning needs code that reads time from one injectable source. Where the code calls the system clock in many places, shifting the data is the practical choice. If the payment sandbox uses a test clock, set its frozen time to the date the application sees. Record the choice in the manifest, because a tester reading a failure needs to know which day the application believed it was.

Confirm the target before anything is dropped

Every reset command above overwrites, truncates or drops data, and the login that runs it can often do the same to production. Put the target check inside the script, where it runs every time, and run the script with sqlcmd -b, which exits on the first error so later batches never start:

-- First batch of the SQL Server reset script
IF @@SERVERNAME <> N'QA-SQL-01'
   OR NOT EXISTS (SELECT 1 FROM Larkspur.dbo.EnvironmentMarker WHERE Name = N'qa')
    THROW 50001, N'Reset refused: target is not the Larkspur QA database.', 1;

The marker is a table with one row, added when the baseline is captured. Production never has it, so a mistyped server name or a copied connection string stops at the first batch. Credentials cover what the script cannot: when the reset job's secret store holds QA logins only, no edit to the script can reach production.

Petrichor can also gate access by team, so a Windows test team sees only Windows environments. Before resetting a shared environment, check for active test sessions: sys.dm_exec_sessions filtered to the test logins on SQL Server, or pg_stat_activity on PostgreSQL, shows whether someone is in the middle of a run.

Which checks must pass before testers log back in?

A reset that reports success has proven that the script reached its last line. Petrichor's engineers keep watching their own automation for that reason: a job can finish green while the service it started has already stopped answering requests. Read the manifest back from the running environment:

• The application's version endpoint returns the build and commit in the manifest.
• Migration history agrees: the newest row in __EFMigrationsHistory for Entity Framework Core, or flyway_schema_history for Flyway, matches schema_version.
• Row counts for the tables the manifest lists match exactly.
• A checksum over reference tables matches the one recorded at capture, such as SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.Plans; on SQL Server. BINARY_CHECKSUM can collide, so two different tables can occasionally produce the same checksum.
• Queues and the mail catcher are empty, and the jobs the reset disabled are still disabled.
• One base user signs in and reaches the scenario's first screen.

One more check takes a second run: run the scenario, reset, run it again, and diff the two results field by field. Any field that differs points at state the reset missed.

Long UAT cycles and other workloads to leave alone

Resets after every run suit regression and validation work. Other QA goes better with the state left in place:

• User acceptance testing where business users work a scenario across several days. A reset during the cycle erases their progress, so book the environment for the cycle and reset between cycles.
• Soak and performance tests, which measure the system after caches warm and indexes grow. A reset before each run measures a cold start.
• Unscripted testing sessions, where the state a tester builds up is often how the bug surfaces. Take a snapshot at the start, investigate afterward, and reset when the session closes.
• Datasets the size of production, where the restore outlasts the test. At an assumed sustained 200 MB/s, a 400 GB backup takes about 34 minutes to restore before the first test starts.

That last case can favor staying on AWS when the data already sits in Amazon Aurora. An Aurora clone of a cluster that large is created without copying data: the clone shares pages with its source and stores only the pages that change afterward. AWS's documentation allows 15 such clones per source before the next one becomes a full copy. Petrichor prefers to start new customers with dev and QA and leave production where it is, often on AWS. A QA process built on fresh clones of an Aurora cluster holding several terabytes is the exception to that order, and it should stay on AWS beside the data.

Book a call with a consultant and bring the reset steps your team runs today, including the ones someone still does by hand.