Before you begin
Complete SQL basics. Connect SSMS or another SQL client to a disposable SQL Server instance with a lab login. Catalog visibility depends on permissions. These first queries are read-only.
Working toward: Operate a database with documented access, maintenance, and a verified restore.
Read each explanation, run the example in your own lab, and attempt the exercise before opening its answer. Published lessons are ready to study; unfinished roadmap topics remain planned.
Validation: Based on upstream documentation. Platform-specific labs require the learner’s matching environment and are not execution-tested here.
1. Instance versus database
An instance hosts databases. A login authenticates to the instance; a database user controls access within a database, subject to the chosen authentication design. Query the target and current context before doing anything else.
SELECT @@SERVERNAME AS instance_name, DB_NAME() AS database_name;
SELECT SUSER_SNAME() AS login_name, USER_NAME() AS database_user;What to expect
Your instance, selected database, login and database user. Their names need not match.
Your turn
Why can a login connect but fail to read a table in another database?
Show answer and reasoning
Connection permission does not imply table permission or a correctly mapped database user. Inspect the specific database’s user and roles.Watch for: Do not grant sysadmin to bypass a missing table permission.
Link to this lesson2. Read database state
The system catalog exposes database state and recovery model. Visibility is permission-dependent. ONLINE says the database is available at the engine level, not that every application workflow succeeds.
SELECT name, state_desc, recovery_model_desc
FROM sys.databases
ORDER BY name;What to expect
One row for each visible database, including state and recovery model.
Your turn
Does FULL recovery model alone guarantee point-in-time recovery?
Show answer and reasoning
No. You need a valid backup/log chain and the relevant backups, plus a working restore procedure. Recovery model is only part of the design.Watch for: Do not switch recovery models casually; it can affect the log backup chain and recovery options.
Link to this lesson3. Plan an isolated restore
A backup plan must say where data can be restored and which files, keys and permissions are required. Restoring over the source destroys the very evidence you are trying to protect; start with a distinct lab destination.
What to expect
A recovery checklist: backup set, encryption keys if used, destination names and paths, target time, validation query and elapsed-time record.
Your turn
A backup file exists but its decryption key is missing. Is it a usable recovery copy?
Show answer and reasoning
Not necessarily. Preserve and test required keys, certificates, credentials and restore tooling separately. Verify the application after restoring, not merely the backup job status.Watch for: A successful backup job or metadata-only check is not equivalent to a completed restore.
Link to this lessonPath to advanced
In-progress stages identify the lessons already published. All other listed topics remain planned. Each addition needs teaching, a reproducible lab, failure cases, and a checkpoint before the capstone.
Access and storage
Logins versus users, roles, files, capacity and service identity.
PLANNEDDiagnose
Blocking, execution plans, waits and job history using least-privilege monitoring.
PLANNEDRecovery
Full/differential/log backups, point-in-time recovery and restore drills.
PLANNED
References
Original AUWEN lessons, with upstream documentation for further study and version checks.
All learning paths and update notes →