Learning Microsoft SQL Server and its backup mechanism

Learning Microsoft SQL Server

This document is a set of Microsoft SQL Server learning notes. The online store is only a small case study that gives us related data to work with: products, customers, orders, and order details. The focus is not building an online-store application, but understanding how SQL Server works and how to protect its data from loss.

The sequence moves from foundations to operations: run SQL Server locally, read data with T-SQL, understand database files and the transaction log, choose a recovery model, create backups, and test restore.

1. Set up a SQL Server lab

Docker Compose makes SQL Server easy to create and recreate during practice. The volume keeps database data outside the container lifecycle; it is not a replacement for a backup.

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "Password123!"
    ports:
      - "1433:1433"
    volumes:
      - mssql_data:/var/opt/mssql

volumes:
  mssql_data:

The password here is for local practice only. A real deployment should read credentials from a secret manager instead of committing them to a Compose file.

2. An online store as the T-SQL practice case

The riset model uses five core tables. Categories groups products, Customers stores buyers, Orders represents transactions, and OrderItems stores the products included in each order. This ERD is not the final goal of this document; it simply provides practice data for SELECT, JOIN, aggregation, subqueries, and LEFT JOIN in SQL Server.

Merender diagram...

In OrderItems, UnitPrice is the product price when the order was placed, not the current catalog price. That keeps transaction history stable when catalog prices change.

3. Practice queries with T-SQL

The exercises move from simple filters to aggregations and subqueries. A few representative examples:

Filtering and sorting

SELECT *
FROM Products
WHERE Price < 200000
ORDER BY Price ASC;

SELECT *
FROM Customers
WHERE City IN ('Jakarta', 'Bandung');

Joining entities

SELECT p.ProductName, c.CategoryName
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID;

SELECT o.OrderID, o.OrderDate, c.FullName
FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID;

Order details require two joins. The subtotal uses quantity multiplied by the historical item price.

SELECT
    p.ProductName AS product_name,
    oi.Quantity AS quantity,
    oi.Quantity * oi.UnitPrice AS subtotal
FROM Orders o
JOIN OrderItems oi ON oi.OrderID = o.OrderID
JOIN Products p ON p.ProductID = oi.ProductID
WHERE o.OrderID = 1;

Aggregation for business questions

SELECT c.CategoryName, COUNT(p.ProductID) AS product_count
FROM Products p
JOIN Categories c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryName;

SELECT o.Status, SUM(oi.Quantity * oi.UnitPrice) AS revenue
FROM Orders o
JOIN OrderItems oi ON oi.OrderID = o.OrderID
GROUP BY o.Status;

WHERE filters rows before grouping, while HAVING filters aggregate results. For example, customers who spent more than five million:

SELECT c.FullName, SUM(oi.Quantity * oi.UnitPrice) AS total_spend
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON oi.OrderID = o.OrderID
WHERE o.Status IN ('SHIPPED', 'PAID')
GROUP BY c.CustomerID, c.FullName
HAVING SUM(oi.UnitPrice * oi.Quantity) > 5000000;

Subqueries and left joins

The average price can be used to compare every product against the catalog average:

SELECT *
FROM Products p
WHERE p.Price > (SELECT AVG(p2.Price) FROM Products p2);

To find customers who have never placed an order, a LEFT JOIN keeps the customer even when the order side is empty.

SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

The remaining exercises cover products with the lowest stock, categories with more than two products, the best-selling product, average order value, and the city with the highest revenue. The business case can change, but the T-SQL skills stay the same: understand the grain of the data first, then choose the right JOIN, GROUP BY, and metric.

4. What is actually backed up?

SQL Server stores a database in several main components:

Data files (.mdf and .ndf) store tables, indexes, and database objects.
The transaction log (.ldf) records transactions in order before their changes are considered durable.
Backup media (.bak or .trn) stores recovery copies and should ideally live on storage separate from the primary database.

When a transaction changes data, SQL Server writes the log first. This is write-ahead logging. The transaction log is therefore not merely an application log; it is part of consistency and point-in-time recovery.

A database backup reads the data and metadata needed to create a consistent copy. A log backup reads log records not included in the previous log backup. Restore then rebuilds the data files and reapplies those log changes in order.

5. The recovery model determines the backup mechanism

A query cannot recover data that has already disappeared. Backups need to answer two operational targets:

RPO (Recovery Point Objective): how much data may be lost, for example at most 15 minutes.
RTO (Recovery Time Objective): how quickly the service must be online again, for example within one hour.

The recovery model determines which backups are available:

Model	Transaction log	Supported backups	Point-in-time restore
`SIMPLE`	Truncated automatically	Full, differential	No
`FULL`	Kept until backed up	Full, differential, log	Yes
`BULK_LOGGED`	Minimal during bulk operations	Full, differential, limited log	Limited

Inspect the active recovery model with:

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'riset';

6. Backup types and the backup chain

Each backup type has a different role:

Full backup copies the entire database as a recovery baseline.
Differential backup stores changes since the latest full backup. A new differential does not depend on the previous differential.
Transaction log backup stores log records since the previous log backup. It requires the FULL or BULK_LOGGED recovery model.
Copy-only backup creates an extra copy without changing the main backup chain.

Backups do not stand alone. A full backup is the base, a differential points to that full backup, and log backups continue transaction changes in order.

Merender diagram...

Restore order matters. The latest differential is still based on the latest full backup, not the previous differential. If a log backup in the middle of the chain is missing, point-in-time recovery cannot continue past that point.

The point-in-time restore sequence is:

Restore the full backup with NORECOVERY.
Restore the latest differential with NORECOVERY.
Restore each log backup in order.
Restore the final log with STOPAT when recovering to a point before a mistake.
Use WITH RECOVERY on the final restore to bring the database online.

7. Create backups in SQL Server

-- Full backup: base of the chain
BACKUP DATABASE riset
TO DISK = '/var/opt/mssql/backup/riset_FULL.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;

-- Differential: changes since the latest full backup
BACKUP DATABASE riset
TO DISK = '/var/opt/mssql/backup/riset_DIFF_20260519.bak'
WITH DIFFERENTIAL, COMPRESSION, INIT;

-- Transaction log: changes since the previous log backup
BACKUP LOG riset
TO DISK = '/var/opt/mssql/backup/riset_LOG_1.trn'
WITH COMPRESSION, INIT;

-- Copy-only: ad hoc backup without changing the main chain
BACKUP DATABASE riset
TO DISK = '/var/opt/mssql/backup/riset_ADHOC.bak'
WITH COPY_ONLY, COMPRESSION, INIT;

COPY_ONLY is useful before a deployment or migration when we need an extra snapshot without changing the position of the differential or log chain.

8. Monitor and test restore

A backup completing without an error does not prove it has ever been restored. Backup metadata in msdb helps inspect history and physical file locations.

SELECT TOP 10
    database_name,
    CASE type
        WHEN 'D' THEN 'FULL'
        WHEN 'I' THEN 'DIFFERENTIAL'
        WHEN 'L' THEN 'LOG'
    END AS backup_type,
    backup_start_date,
    backup_finish_date,
    backup_size / 1024.0 / 1024.0 AS size_mb
FROM msdb.dbo.backupset
WHERE database_name = 'riset'
ORDER BY backup_start_date DESC;

A backup file existing on disk is not enough. Backups should be monitored through SQL Server metadata and restored into a separate database or environment for testing.

The minimum learning checklist is:

Define RPO and RTO before choosing a backup schedule.
Make sure the recovery model supports the required point-in-time restore.
Store backup files separately from the primary database files.
Monitor backup success, size, timestamps, and physical paths.
Run restore drills regularly instead of only checking that a .bak file exists.

Closing

The online-store case only makes the exercises concrete. The core of this R&D is understanding SQL Server as a database engine: how data is stored, how transactions are recorded in the log, how the recovery model affects backups, and how the backup chain is used during restore.

A production-ready database is not merely a database that accepts INSERT. It has consistent backups, a traceable chain, and a restore procedure that has actually been run.

Find something

Learning Microsoft SQL Server and its backup mechanism