The test machine works until somebody asks how to build the next one. A test server from a script turns setup choices, database settings, and seed data into a repeatable process that another DBA can inspect.
Write Down the Target Before Installing
Decide what the server must reproduce. Engine version, edition, instance name, collation, compatibility level, service accounts, and database options all affect a test. A copy of production data does not repair an instance built with different settings. Record the purpose of the environment and the differences that are intentional. Keep a short acceptance checklist next to the build script.
I start with the smallest server that can answer the question. A query plan test needs representative indexes, statistics, and row distribution. A deployment rehearsal needs permissions, jobs, and the right database options. Installing every optional component makes a test server slower to build and harder to explain. Which behavior are you trying to reproduce?
Use Supported Media for a Test Server From a Script
Keep the approved SQL Server installation media in a controlled local location. Match its version and edition to the test plan. For a current version, generate a fresh ConfigurationFile.ini from Setup and review every selected option. Setup parameters change across versions, so a file copied from an old install is a risky shortcut. Store the reviewed file without passwords.
The install step can run unattended from Windows PowerShell. The example assumes the media and configuration file already exist and that the license terms have been reviewed. Run it in an elevated session on a disposable test machine. Check the Setup log and process exit code before treating the installation as complete. A quiet installer is still capable of a loud failure later.
# PowerShell
& 'C:\SQLMedia\setup.exe' /Q /ACTION=Install /ConfigurationFile='C:\SQLBuild\ConfigurationFile.ini' /FEATURES=SQL /INSTANCENAME=SQLTEST /ADDCURRENTUSERASSQLADMIN /IACCEPTSQLSERVERLICENSETERMS /SUPPRESSPRIVACYSTATEMENTNOTICE
if ($LASTEXITCODE -ne 0) { throw "SQL Server Setup failed with exit code $LASTEXITCODE" }Apply Instance Settings Deliberately
Installation is only the first step. Configure network access, memory limits, tempdb, backup paths, and service startup according to the environment’s purpose. Make each setting an explicit input to the build rather than an undocumented click. Some changes require a service restart, so the build should include that step and a check afterward. Protect secrets outside the checked-in configuration.
I compare desired settings with what SQL Server reports. A script that only issues sp_configure commands can finish while a required option remains at its previous value. This read-only query shows the configured and active values. Review the differences after the service starts.
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN (N'max server memory (MB)', N'cost threshold for parallelism',
N'max degree of parallelism')
ORDER BY name;
Create Databases From a Known Baseline
Choose whether the database comes from a scripted schema, a backup restored into the test environment, or a build package. A schema-only build is good for deployment tests but lacks realistic data. A restored copy can preserve useful statistics and distributions, but needs privacy controls and a repeatable cleanup. State which one the test requires. Do not quietly mix them between runs.
After creating the database, verify compatibility level, recovery model, collation, and required objects. A successful restore does not mean jobs, logins, credentials, and external dependencies came along. I keep those checks in the acceptance script. The test environment should fail clearly when a required dependency is missing rather than producing a misleading test result.
SELECT name, compatibility_level, recovery_model_desc,
collation_name, state_desc
FROM sys.databases
WHERE name = N'TestLab';Seed Data Without Copying Secrets
For functional tests, seed a small, deterministic set of rows with stable keys. Include normal records, NULL cases, duplicates where allowed, and values at important boundaries. For performance tests, use synthetic data shaped like the real workload. Random strings of equal length rarely reproduce skewed customer or order activity. Document the generator rules and the expected row relationships.
A seed script should be safe to rerun on a fresh database. Use explicit transactions for related inserts and check expected keys afterward. I prefer a named baseline over a folder of scripts labeled final, final2, and final-really. Do not put production credentials or private customer data into the seed package. A test server built from a script is easier to share when its data can be explained.
Prove a Test Server From a Script Can Be Repeated
Build a second disposable machine from the same inputs. Compare the instance settings, database metadata, object inventory, and sample data checks with the first run. Record build duration on your own hardware if speed matters, but do not promise a fixed number of minutes from an untested script. The useful target is repeatability with a short, predictable manual checklist.
What happens when the build stops halfway through? Give each stage a clear failure message and a restart rule. Some steps can be rerun safely, while others require deleting the disposable instance and starting over. Save Setup logs and validation output with the run identifier. A script without its result is only a plan.
Reset and Maintain a Test Server From a Script
A test server from a script needs a reset path as much as a build path. Decide whether each run restores a baseline backup, replays a schema build, or reinitializes sample rows. Test the reset before relying on the environment for a migration rehearsal. If an external service receives messages, resetting SQL Server alone will not reset that side effect.
Keep the approved media, configuration, seed scripts, and validation queries together. Review them after patches and feature changes. I run a short smoke test after every rebuild: connect under the intended login, query a known object, and check the expected database settings. When those checks pass, the environment is ready for the question it was built to answer.
For a repeatable test server, keep a manifest of database name, compatibility level, collation, file locations and the seed data version beside the build script. A successful install does not prove the fixture is ready. Run a short smoke test that connects with the intended login, creates a disposable object, executes the target workload and restores the starting state. I include expected row counts only when they are properties of the fixture, not invented measurements of performance.
The reset path deserves the same review as the creation path. A test database left with half a migration applied can make the next test fail for the wrong reason. I prefer a restore or deterministic reseed that takes the environment to a named baseline. When that reset fails, stop the test rather than continuing on an unknown state. The purpose of a scripted server is not to eliminate human judgment; it is to make the starting conditions visible to every person using it.
Related reading on this blog: Install AdventureWorks and WideWorldImporters: Updated 2026 and How to Install SQL Server 2019? Interview Question of the Week #287: SQL in Sixty Seconds #092.
A test server is not a saved machine image, it is a build you can repeat and verify.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
