SQL Server Administration: Backup, Recovery, Indexing, Transactions & Security

Full backup

A complete copy of the database (data plus enough log to recover it); the base for all other backups

Differential backup

All data pages changed since the last full backup

Transaction log backup

A backup of the log records since the last log backup; enables point-in-time recovery and truncates the log (FULL/BULK_LOGGED models)

Simple recovery model

Recovery model where log auto-truncates, no log backups, no point-in-time restore

Full recovery model

Recovery model where all transactions fully logged, point-in-time restore with log backups

Bulk-logged recovery model

Recovery model with minimal logging for bulk operations

Restore with no recovery

Restore with _____ leaves the DB in a restoring state so more backups can be applied

Restore with recovery

Restore with _____ brings the DB online

RPO (recovery point objective)

Max acceptable data loss, measured in time

RTO (recovery time objective)

Max acceptable downtime

High availability

Minimizes downtime from local failures, e.g. Failover Cluster or Always On AG

Disaster recovery

Restores service after a major loss, e.g. a site outage, using offsite backups, log shipping or an async replica

Always on availability groups

HA/DR feature that replicates a set of databases from a primary replica to one or more secondary replicas, with automatic or manual failover; secondaries can be readable

Failover cluster instance

A SQL Server instance installed across multiple nodes of a Windows cluster that uses shared storage; if one node fails, the instance restarts on another

Log shipping

Automatically backing up the log, copying it to a secondary server and restoring it there on a schedule; a simple DR solution with manual failover

Clustered index

Index that defines the physical order of table data (one per table, the leaf level is the data); created by default by primary keys

Nonclustered index

Index with a separate structure with key values plus a pointer back to the row (many per table)

Reorganize

Index fragmentation: lighter, online, defragments leaf pages in place (roughly 5-30% fragmentation)

Rebuild

Index fragmentation: drops and recreates the index, updates statistics (roughly >30%)

Statistics

Metadata about data distribution in columns and indexes that the query optimizer uses to estimate row counts and pick an execution plan

Execution plan

The optimizer's chosen strategy for running a query: which indexes, join types and operators it uses; read it to spot scans, key lookups and costly operators

Blocking

One session waits for locks held by another and resolves when the holder finishes

Deadlock

Two sessions each hold a lock the other needs, so SQL Server kills one as the 'victim'

ACID

Atomicity, Consistency, Isolation, Durability: the properties that make database transactions reliable

Atomicity

A transaction is treated as a single, indivisible unit; either every operation inside the transaction succeeds, or the entire transaction is rolled back (undone) with zero partial changes saved to the database.

Consistency

A transaction takes the database from one valid state to another, strictly obeying all predefined rules, constraints, foreign keys, and triggers; if any rule is violated, the transaction is canceled and the data is reverted.

Isolation

Concurrent transactions run independently without interfering with one another; the intermediate, unfinished changes of one transaction remain invisible to others until that transaction is officially committed

Durability

Once a transaction is committed, its changes are permanently saved—typically written to non-volatile memory or a write-ahead log; if the server crashes or loses power immediately afterward, the data remains intact upon reboot

Transaction isolation levels

Control how much a transaction sees of other transactions' changes. READ COMMITTED is the default; READ UNCOMMITTED allows dirty reads (NOLOCK); SNAPSHOT / RCSI uses row versioning in tempdb so readers don't block writers

Stored procedure

Can modify data, return multiple result sets and use output parameters, called with EXEC

User-defined function

Must return a value (scalar or table), can be used inside queries, and cannot modify data

Login

A server-level principal that authenticates to the instance (Windows or SQL auth)

User

A database-level principal mapped to a login that gets permissions in that database

Principle of least privilege

Grant only the minimum permissions needed, using roles (db_datareader, db_datawriter, custom roles) and Windows authentication/AD groups where possible; also consider TDE for data at rest, encrypted backups, and auditing

SQL Server Agent

The service that schedules and runs jobs (backups, index maintenance, SSIS packages, T-SQL scripts), with alerts and operator notifications

SSIS (SQL Server Integration Services)

An ETL (extract, transform, load) platform for building packages that move and transform data between sources and destinations; deployed to the SSIS catalog and often run via SQL Agent jobs

INNER JOIN

Join matching rows in both tables

LEFT JOIN

Join every left row plus matches, unmatched right columns are NULL

RIGHT JOIN

Join every right row plus matches, unmatched left columns are NULL

FULL OUTER JOIN

Join all rows from both, matched where possible, NULL-filled otherwise

WHERE

Filters individual rows before grouping and aggregation

HAVING

Filters groups after grouping and aggregation

Primary key

Uniquely identifies each row and can't be NULL

Foreign key

Enforces a relationship to a key in another table (referential integrity)

Composite key

Made of multiple columns, e.g. (characterid, abilityid) in a junction table

Aggregate functions

Functions like COUNT, SUM, AVG, MIN, MAX that calculate one value over a set of rows

GROUP BY

Combines rows with the same grouping values so aggregates are computed per group

UNION

Combines results and removes duplicate rows

UNION ALL

Keeps duplicates and is generally faster because it skips duplicate elimination

DELETE

Removes selected rows (optional WHERE, can fire delete triggers)

TRUNCATE

Removes all rows efficiently (no WHERE, generally less logging, restrictions like foreign-key references)

DROP

Removes the table definition and its data

NULL

A missing, unknown, or inapplicable value

COALESCE

Returns the first non-NULL expression in a list (standard SQL)

ISNULL

The SQL Server-specific two-argument version of COALESCE()

View

Named query that behaves like a virtual table (a regular view stores its definition, not result data)

Normalization

Organizing data into related tables to reduce duplication and prevent update anomalies

1NF

Single values, no repeating groups

2NF

1NF plus non-key columns depend on the whole key

3NF

2NF plus no transitive dependencies

BEGIN TRANSACTION

Starts a transaction

COMMIT

Makes changes permanent

ROLLBACK

Undoes uncommitted changes

Index

A data structure that helps SQL Server locate rows efficiently, like a book's index. Pros: faster reads, joins, filtering, sorting. Cons: uses storage and adds overhead to inserts, updates and deletes

Common causes of slow queries

Missing or unsuitable indexes, outdated statistics, returning too many rows or columns, blocking or deadlocks, CPU/memory/disk I/O pressure, inefficient joins or logic, parameter-sensitive behavior

SSMS

Microsoft's graphical tool for connecting, running queries, managing objects, reviewing execution plans and admin tasks

T-SQL

Microsoft's SQL dialect with extra programming and admin features

Master

Server-level config, logins, database metadata

Model

Template for new databases

MSDB

Agent jobs, job history, backup history

Tempdb

Temporary objects and internal workspace