Build a Reproducible Lab with Databases, Logins, Sample Data, Backups, and Safety Rules

Learning outcomes

The first four lessons established what SQL Server is, what edition/platform the lab uses, and how to verify connectivity. The final foundation task is to create a reproducible course baseline. Every later chapter needs the same database names, deterministic rows, least-privilege principal, compatibility/recovery assumptions, backup location conventions, and safe reset procedure so that locking, Query Store, indexing, HA, security, columnstore, and vector experiments do not turn into one-off demos.

1. Declare the baseline before creating data

This course uses SQL Server 2025 (17.x) Developer/Express for mandatory local work. Record the exact build rather than assuming everyone is on the same CU. For the baseline database, use the SQL Server 2025 compatibility level supported by the local engine when available and explicitly record it. The lab begins in SIMPLE recovery so learners do not accidentally believe they have a usable log-backup chain; Chapter 15 deliberately changes recovery model in a recovery lab and explains the consequences.

Collation is not silently normalized. The database can inherit the instance default for this baseline, but the manifest records the resulting database collation. Later migration/string lessons can create a separate database to demonstrate collation conflicts without destabilizing the common baseline.

2. Create the database and deterministic ServiceHub schema

USE master;GOIF DB_ID(N'ServiceHubLab') IS NULLBEGIN    CREATE DATABASE ServiceHubLab;END;GOALTER DATABASE ServiceHubLab SET RECOVERY SIMPLE;GO-- SQL Server 2025 supports compatibility level 170. If your lab is not-- actually SQL Server 2025, stop and use the level documented for that engine.ALTER DATABASE ServiceHubLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubLab;GOCREATE SCHEMA ops AUTHORIZATION dbo;GOCREATE TABLE ops.Technician(    technician_id int IDENTITY(1,1) NOT NULL        CONSTRAINT PK_Technician PRIMARY KEY,    technician_code varchar(12) NOT NULL        CONSTRAINT UQ_Technician_Code UNIQUE,    display_name nvarchar(100) NOT NULL,    region_code char(3) NOT NULL,    is_active bit NOT NULL        CONSTRAINT DF_Technician_Active DEFAULT (1),    created_at datetime2(0) NOT NULL        CONSTRAINT DF_Technician_Created DEFAULT SYSUTCDATETIME());GOCREATE TABLE ops.WorkOrder(    work_order_id bigint IDENTITY(1001,1) NOT NULL        CONSTRAINT PK_WorkOrder PRIMARY KEY,    technician_id int NULL,    customer_code varchar(16) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    scheduled_at datetime2(0) NULL,    closed_at datetime2(0) NULL,    description nvarchar(400) NOT NULL,    row_version rowversion NOT NULL,    CONSTRAINT FK_WorkOrder_Technician        FOREIGN KEY (technician_id)        REFERENCES ops.Technician(technician_id),    CONSTRAINT CK_WorkOrder_Status        CHECK (status IN ('new','assigned','onsite','closed','cancelled')),    CONSTRAINT CK_WorkOrder_Priority        CHECK (priority BETWEEN 1 AND 5),    CONSTRAINT CK_WorkOrder_ClosedTime        CHECK (closed_at IS NULL OR closed_at >= opened_at));GO

The seed uses fixed timestamps so plans and result sets are reproducible. rowversion is a monotonically changing binary version marker generated by SQL Server; it is not a date/time. Chapter 3 revisits data types in depth.

INSERT ops.Technician    (technician_code, display_name, region_code, is_active, created_at)VALUES    ('T-100','Mina Rahimi','N01',1,'2026-08-01T08:00:00'),    ('T-101','Owen Brooks','W02',1,'2026-08-01T08:00:00'),    ('T-102','Sara Chen','E03',1,'2026-08-01T08:00:00');GOINSERT ops.WorkOrder    (technician_id, customer_code, status, priority,     opened_at, scheduled_at, closed_at, description)VALUES    (1,'CUST-001','assigned',2,'2026-08-10T08:00:00','2026-08-10T10:00:00',NULL,N'Inspect cooling pump'),    (2,'CUST-002','closed',1,'2026-08-10T09:00:00','2026-08-10T11:00:00','2026-08-10T12:10:00',N'Replace pressure sensor'),    (NULL,'CUST-003','new',4,'2026-08-11T07:30:00',NULL,NULL,N'Diagnose intermittent gateway'),    (3,'CUST-004','onsite',3,'2026-08-11T08:15:00','2026-08-11T09:30:00',NULL,N'Calibrate flow meter');GOSELECT w.work_order_id, w.customer_code, w.status, w.priority,       t.technician_code, t.display_name, w.opened_atFROM ops.WorkOrder AS wLEFT JOIN ops.Technician AS t  ON t.technician_id = w.technician_idORDER BY w.work_order_id;GO

3. Create a least-privilege application principal

A login is an instance-level security principal that can authenticate to the Database Engine. A database user maps an identity into a database. The application should not connect as sa, a Windows administrator, or another sysadmin. For this local lab, create one SQL login and grant only the schema permissions required by the starter workload.

Do not commit a password in the SQL file. The example uses a sqlcmd scripting variable. Variable substitution is performed by sqlcmd/SQLCMD mode before T-SQL reaches the server; $(LabLoginPassword) is not a T-SQL variable.

-- Run this script through sqlcmd/SQLCMD mode with -v LabLoginPassword="..."USE master;GOIF SUSER_ID(N'servicehub_app') IS NULLBEGIN    CREATE LOGIN servicehub_app      WITH PASSWORD = '$(LabLoginPassword)',           CHECK_POLICY = ON,           CHECK_EXPIRATION = OFF;END;GOUSE ServiceHubLab;GOIF USER_ID(N'servicehub_app') IS NULL    CREATE USER servicehub_app FOR LOGIN servicehub_app;GOCREATE ROLE servicehub_rw AUTHORIZATION dbo;GOALTER ROLE servicehub_rw ADD MEMBER servicehub_app;GOGRANT SELECT, INSERT, UPDATE ON SCHEMA::ops TO servicehub_rw;GRANT EXECUTE ON SCHEMA::ops TO servicehub_rw;GO

Why no blanket db_owner? Because future security lessons need a meaningful permission boundary. The starter app can read/insert/update operational rows but cannot create arbitrary logins, change server settings, restore databases, or automatically own every object in the database.

4. Capture the environment manifest

USE ServiceHubLab;GOSELECT    SYSDATETIMEOFFSET() AS observed_at,    SERVERPROPERTY('ServerName') AS server_name,    SERVERPROPERTY('Edition') AS edition,    SERVERPROPERTY('ProductVersion') AS product_version,    SERVERPROPERTY('ProductLevel') AS product_level,    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,    d.name AS database_name,    d.compatibility_level,    d.recovery_model_desc,    d.collation_name,    d.is_query_store_onFROM sys.databases AS dWHERE d.name = N'ServiceHubLab';SELECT    SCHEMA_NAME(t.schema_id) AS schema_name,    t.name AS table_name,    SUM(p.rows) AS rowsFROM sys.tables AS tJOIN sys.partitions AS p  ON p.object_id = t.object_id AND p.index_id IN (0,1)WHERE t.schema_id = SCHEMA_ID(N'ops')GROUP BY t.schema_id, t.nameORDER BY t.name;GO

Store this output with your lab notes. It is the baseline that later performance observations must cite. If a future plan behaves differently, compare build, compatibility level, Query Store state, edition, dataset size, and configuration before claiming a regression.

5. Create a real baseline backup

A database that exists only on the live data files is not recoverable from accidental destruction. Create a full backup to a directory writable by the SQL Server service account/process. The path is server-side: a path visible to your laptop is not automatically visible inside a Linux container or remote Windows server.

-- Set BackupPath through sqlcmd/SQLCMD mode to a server-visible .bak path.BACKUP DATABASE ServiceHubLabTO DISK = '$(BackupPath)'WITH COPY_ONLY,     INIT,     CHECKSUM,     STATS = 10;GORESTORE VERIFYONLYFROM DISK = '$(BackupPath)'WITH CHECKSUM;GO

RESTORE VERIFYONLY is useful media/backup-structure validation, but it is not proof that a disaster recovery procedure works. Chapter 15 restores backups into disposable databases and performs integrity/application checks. COPY_ONLY keeps this training baseline from changing a production-style differential base if the same instance later demonstrates backup chains.

6. Deliberately wrong approach: “reset” by dropping whatever database is current

A dangerous reset script uses DROP DATABASE $(DatabaseName) with an unchecked variable. One typo can destroy the wrong database. A safer reset has a hard-coded disposable name, verifies the target exists, switches to master, and makes the destructive step obvious.

USE master;GOIF DB_ID(N'ServiceHubLab') IS NULL    THROW 50001, 'ServiceHubLab does not exist; reset aborted.', 1;GOSELECT    SERVERPROPERTY('ServerName') AS server_name,    DB_NAME(DB_ID(N'ServiceHubLab')) AS database_to_reset;GO-- Stop here and verify the two values before destructive reset.-- The actual DROP belongs in an explicitly invoked reset script, not startup code.

For routine course resets, prefer an idempotent seed/reset script that deletes only known lab rows or restores a known-good lab backup. Dropping/recreating the database is acceptable only when you intentionally want to reset database-level state and have verified the target.

7. Verification checklist for every future chapter

• Identity: exact SQL Server edition/build and client tool/driver are recorded.
• Scope: current database is ServiceHubLab before chapter-specific DDL/DML.
• Data: deterministic baseline row counts/results are known before performance or concurrency experiments.
• Security: application code uses a least-privilege principal; administrative steps are separated.
• Recovery: a server-visible backup path exists before destructive database-level experiments.
• Configuration: compatibility level, collation, recovery model, and Query Store state are captured.
• Cleanup: chapter-created objects have an explicit cleanup/reset path.
• Evidence: output is saved before and after a change so conclusions are based on observed state.

8. Production judgment

A reusable lab is a miniature operations contract. Deterministic seed data makes query outputs explainable. Least privilege makes security failures visible. Explicit recovery assumptions prevent false confidence. A backup path and reset procedure keep destructive learning safe. Environment manifests make performance comparisons scientifically meaningful.

Production adds stricter requirements: managed secrets, service accounts, encrypted connections with validated certificates, backup retention/offsite copies, integrity checks, HA/DR, monitoring, capacity planning, change control, and tested restores. The lab intentionally does not pretend those concerns disappear; it creates the vocabulary and evidence habits used later to implement them.

9. Chapter 01 checkpoint and bridge

You can now explain SQL Server’s instance/database boundary, choose a non-production edition intentionally, select a supported platform/toolchain, verify the instance/connection rather than trusting setup, and reproduce a safe ServiceHub lab with deterministic data, least privilege, and backup evidence. Chapter 02 begins inside the instance: services, ports/protocols/endpoints, system databases, files/filegroups, server configuration, collations, compatibility levels, database options, and recovery models.

Authoritative references

• CREATE DATABASE — database creation semantics
• ALTER DATABASE compatibility level — SQL Server 2025 compatibility-level guidance
• CREATE LOGIN — instance login creation and password options
• CREATE USER — database principal mapping
• BACKUP — full backup, COPY_ONLY, checksum, and media options
• sqlcmd utility — scripting and variable context