SQL Server administration

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.

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.

2. 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.

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.

3. 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.

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.

4. Prove a full backup by restoring a separate database

Before this lesson: Complete administration lessons 1–3, basic SELECT/INSERT/UPDATE and the SQL transactions introduction. Use SQL Server 2022 or 2025 Developer on a disposable Windows VM, with SSMS and a lab administrator authorized to create, back up and restore databases. This is not Azure SQL Database or Oracle syntax. Use the same SQL Server version for backup and restore in this first lab.

Outcome: recover a known two-row dataset into a second database and show that it represents the backup moment rather than the current source. A backup-job success means a backup operation completed. VERIFYONLY checks the backup set's readability/completeness and relevant checksums; it does not reconstruct the database and verify its internal structure. A restore followed by DBCC CHECKDB and a business query answers different questions, so the exercise retains all three checks.

Prepare an empty C:\AuwenLab directory on the SQL Server host, not your SSMS laptop. Give the SQL Server Database Engine service account access to read/write that lab directory through Windows folder permissions; identify the actual service identity first. Do not grant Everyone full control. Ensure free space for source, backup and restored files. Reserve the two database names and all paths shown below. If any already exist, choose a fresh VM snapshot rather than overwriting them. The opening guard checks names, but an unregistered file can also occupy a path: inspect the empty directory separately.

Run each numbered SQL file separately in SSMS, on the lab instance, and stop at the first error. Confirm @@SERVERNAME before continuing. Each file uses TRY/CATCH to rethrow runtime errors and stop that stage; the first creates its new database and table through dynamic SQL so the table is compiled only after the database exists. Do not select and run only the statements below a failed guard. The fixture uses integer cents, with a positive-value CHECK constraint and a primary key. Its baseline rows are ticket 1 = 1200 cents and ticket 2 = 800 cents: two rows, 2000 cents total. These are invented lab transactions, not sales observations.

The full backup uses COPY_ONLY so an ad-hoc copy does not establish a new differential base. CHECKSUM requests backup checksums; neither option replaces a restore drill. This first exercise uses a fresh backup path once. Inspect HEADERONLY and confirm the database name, full-backup type, Position 1, finish time and checksum presence before selecting FILE = 1. A reused media file can hold several backup sets: never assume the first is today's. FILELISTONLY supplies logical file names and physical paths recorded in the backup; logical names are identifiers, not destination filenames.

After the backup, the source fixture deliberately changes ticket 2 to 900 cents. Its total becomes 2100. Restore the earlier set as AuwenRecoveryRestored, mapping both logical files to new physical paths with MOVE. The preflight uses the same MOVE destinations; do not add REPLACE to silence a collision. RECOVERY brings this full-only restore online. A later log-chain exercise would keep intermediate steps in NORECOVERY, then recover only at its intended endpoint; that is not part of this lab.

Finally run CHECKDB on the restored database and compare exact business rows. A total alone can conceal the wrong records, so the assertion checks row count and each expected primary-key/value pair. Source total 2100 and restored total 2000 demonstrate which moment was recovered. They do not demonstrate a specific production RPO. Record start/end times and what was omitted: logins, SQL Agent jobs, application secrets, external files and application connectivity may need their own recovery procedures.

Keep both databases until you have reviewed the evidence; clean up by reverting the disposable VM snapshot. This deliberately avoids a generic DROP DATABASE recipe. A real drill should also restore on a separate host and test the application under its actual identity. Encryption certificates/keys and version compatibility require advance planning; you cannot assume a newer-version backup restores onto an older engine.

USE master;
BEGIN TRY
SELECT @@SERVERNAME AS instance_name, @@VERSION AS engine_version;
IF DB_ID(N'AuwenRecoveryLab') IS NOT NULL OR DB_ID(N'AuwenRecoveryRestored') IS NOT NULL
    THROW 51000, 'Reserved lab database exists. Stop and use a fresh disposable VM.', 1;
EXEC(N'CREATE DATABASE AuwenRecoveryLab
 ON PRIMARY (NAME=N''AuwenRecoveryLab_Data'', FILENAME=N''C:\AuwenLab\source.mdf'')
 LOG ON (NAME=N''AuwenRecoveryLab_Log'', FILENAME=N''C:\AuwenLab\source_log.ldf'')');
EXEC(N'USE AuwenRecoveryLab;
CREATE TABLE dbo.Ticket (
    ticket_id int NOT NULL PRIMARY KEY,
    cents int NOT NULL CHECK (cents > 0)
);
INSERT INTO dbo.Ticket (ticket_id, cents) VALUES (1, 1200), (2, 800);
SELECT COUNT(*) AS rows_saved, SUM(cents) AS cents_saved FROM dbo.Ticket;');
END TRY
BEGIN CATCH
    THROW;
END CATCH;

USE master;
BEGIN TRY
BACKUP DATABASE AuwenRecoveryLab
 TO DISK = N'C:\AuwenLab\checkpoint-20260928.bak'
 WITH COPY_ONLY, CHECKSUM;
RESTORE HEADERONLY FROM DISK = N'C:\AuwenLab\checkpoint-20260928.bak';
RESTORE FILELISTONLY FROM DISK = N'C:\AuwenLab\checkpoint-20260928.bak' WITH FILE = 1;
-- Stop here and confirm the backup set and both logical names.
END TRY
BEGIN CATCH
    THROW;
END CATCH;

USE master;
BEGIN TRY
IF DB_ID(N'AuwenRecoveryRestored') IS NOT NULL
    THROW 51001, 'Restore target already exists. Do not overwrite it.', 1;
RESTORE VERIFYONLY FROM DISK = N'C:\AuwenLab\checkpoint-20260928.bak'
 WITH FILE = 1, CHECKSUM,
 MOVE N'AuwenRecoveryLab_Data' TO N'C:\AuwenLab\restored.mdf',
 MOVE N'AuwenRecoveryLab_Log' TO N'C:\AuwenLab\restored_log.ldf';
-- Continue only after successful preflight; caught runtime errors stop this stage.
UPDATE AuwenRecoveryLab.dbo.Ticket SET cents = 900 WHERE ticket_id = 2;
SELECT SUM(cents) AS current_source_cents FROM AuwenRecoveryLab.dbo.Ticket;
RESTORE DATABASE AuwenRecoveryRestored
 FROM DISK = N'C:\AuwenLab\checkpoint-20260928.bak'
 WITH FILE = 1, CHECKSUM, RECOVERY,
 MOVE N'AuwenRecoveryLab_Data' TO N'C:\AuwenLab\restored.mdf',
 MOVE N'AuwenRecoveryLab_Log' TO N'C:\AuwenLab\restored_log.ldf';
END TRY
BEGIN CATCH
    THROW;
END CATCH;

USE master;
BEGIN TRY
DBCC CHECKDB (N'AuwenRecoveryRestored') WITH NO_INFOMSGS;
SELECT ticket_id, cents FROM AuwenRecoveryRestored.dbo.Ticket ORDER BY ticket_id;
SELECT COUNT(*) AS restored_rows, SUM(cents) AS restored_cents
FROM AuwenRecoveryRestored.dbo.Ticket;
IF (SELECT COUNT(*) FROM AuwenRecoveryRestored.dbo.Ticket) <> 2
 OR NOT EXISTS (SELECT 1 FROM AuwenRecoveryRestored.dbo.Ticket WHERE ticket_id = 1 AND cents = 1200)
 OR NOT EXISTS (SELECT 1 FROM AuwenRecoveryRestored.dbo.Ticket WHERE ticket_id = 2 AND cents = 800)
    THROW 51002, 'Restored business rows differ from the backup baseline.', 1;
SELECT 'Business-row check passed' AS result;
END TRY
BEGIN CATCH
    THROW;
END CATCH;

What to expect

At baseline: 2 rows / 2000 cents. After the controlled source edit: 2100 cents. After restore: the original two rows / 2000 cents and “Business-row check passed”. CHECKDB with NO_INFOMSGS should report no errors; inspect its messages, not just the later SELECT. Any missing logical file, permission denial, unavailable backup set, checksum error or CHECKDB error stops acceptance even if some later output looks right.

1. No. It does not restore the database or validate its internal data structure. Preserve its result, then verify a real restore and application data.
2. You may have selected a backup taken after the change, or queried the source database instead of the restored copy. Check HEADERONLY's set metadata and the fully qualified table name; do not rewrite the expected baseline to fit the output.
3. No. The primary key makes ticket_id unique. Two rows are required, and each original key/value pair must exist. A sum-only test could miss compensating errors.
4. Example report structure, with your real values: instance/version; backup path + Position + finish time; logical-to-physical file map; VERIFYONLY result; CHECKDB result; returned rows; elapsed restore/validation time; application-owner follow-up. Do not populate times or success claims from the lesson's predictions.
Ready to continue when the source and restored states are deliberately different, the restored rows match the baseline, CHECKDB has no errors and you can explain what the drill did not recover. Differential/log backups, point-in-time restore, cross-host identity mapping and Oracle recovery remain planned.

Watch for: Server-side file access uses the engine service identity, not your desktop identity. Do not use WITH REPLACE, reuse a backup path without checking set positions, or run all batches past an error. Keep each guarded stage intact when executing it. Do not treat COPY_ONLY as encryption, CHECKDB as an application test, or a VM checkpoint as your only independent backup.

Lesson references

• Microsoft: restore to new names and paths (checked 28 September 2026) ↗
• Microsoft: VERIFYONLY scope and limitations ↗
• Microsoft: COPY_ONLY semantics ↗
• Microsoft: backup logical files ↗
• Microsoft: DBCC CHECKDB ↗