Narcis Miclaus

6 min read

SQL Server, from the ground up Lesson 31 / 40

Backups: FULL, DIFF, LOG, and the restore drill

The three backup types, point-in-time recovery, how to test a backup you've never tested, and the schedule every OLTP system should have.

A backup you’ve never tested is not a backup. It’s a file. It might be a good backup, it might be corrupt, it might be backing up the wrong database — you won’t know until the day you try to restore it. That day is always a Saturday, always when your phone is about to die, and always when the CEO is copied on the email.

This lesson is about building a backup strategy you actually trust. Types of backups, recovery models, the restore drill you should run quarterly, and the EU-specific wrinkle that is data-residency regulation.

The three backup types

FULL backup

A complete copy of the database at a point in time. Contains every data page and enough of the log to make the backup internally consistent.

BACKUP DATABASE Runehold
TO DISK = N'D:\Backups\Runehold_FULL_20260315.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;
  • FORMAT, INIT — overwrite the file if it exists. Safety: we almost always want a fresh backup, not appended.
  • COMPRESSION — much smaller file. Modern SQL Server Enterprise includes compression for free; Standard since 2022.
  • CHECKSUM — verifies pages as they’re backed up; catches corruption earlier.
  • STATS = 10 — print progress every 10%.

Typical Runehold schedule: a FULL backup nightly around 02:00 UTC. Takes about 25 minutes.

DIFFERENTIAL backup

Everything that’s changed since the last FULL. Smaller and faster than a FULL, but depends on the FULL being available.

BACKUP DATABASE Runehold
TO DISK = N'D:\Backups\Runehold_DIFF_20260315_12.bak'
WITH DIFFERENTIAL, FORMAT, INIT, COMPRESSION, CHECKSUM;

Common pattern: FULL nightly, DIFF every 6 hours. Restore involves FULL + the latest DIFF — two files instead of many.

LOG backup

All log records since the last log backup. Essential for point-in-time recovery and for keeping the log file from growing unboundedly in FULL recovery model.

BACKUP LOG Runehold
TO DISK = N'D:\Backups\Runehold_LOG_20260315_1245.trn'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM;

Typical schedule: every 15 minutes during business hours. That means if the database fails, you lose at most 15 minutes of changes (RPO — Recovery Point Objective = 15 min).

Recovery models again

From lesson 30:

  • SIMPLE — no log backups possible. RPO = last FULL or DIFF.
  • FULL — log backups required; point-in-time recovery; log stays small between backups.
  • BULK_LOGGED — niche.

Rule: any production database that contains data you’d cry over losing should be in FULL recovery with regular log backups. Everything else — reporting DW, dev, test, data-lake scratch — can be SIMPLE.

Restoring: the actual drill

You cannot say you have backups if you haven’t done a restore. Here’s the drill every team should run at least quarterly.

Step 1: restore the latest FULL

RESTORE DATABASE Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_FULL_20260315.bak'
WITH
    MOVE N'Runehold'     TO N'E:\RestoreTest\Runehold.mdf',
    MOVE N'Runehold_log' TO N'E:\RestoreTest\Runehold_log.ldf',
    NORECOVERY,
    REPLACE,
    STATS = 5;
  • MOVE — because you’re restoring to a different location (or different machine) with different paths. Without MOVE, SQL Server tries to put the files where they were originally, which fails if the path doesn’t exist.
  • NORECOVERY — leave the database in “restoring” state; ready to apply more backups.
  • REPLACE — overwrite if a database with the same name exists (rare, but useful).

Step 2: apply the latest DIFF

RESTORE DATABASE Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_DIFF_20260315_12.bak'
WITH NORECOVERY;

Step 3: apply log backups to the target point in time

RESTORE LOG Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_LOG_20260315_1215.trn'
WITH NORECOVERY;

RESTORE LOG Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_LOG_20260315_1230.trn'
WITH NORECOVERY;

-- Stop at a specific moment
RESTORE LOG Runehold_Recovery
FROM DISK = N'D:\Backups\Runehold_LOG_20260315_1245.trn'
WITH STOPAT = N'2026-03-15 12:38:42', NORECOVERY;

Step 4: bring the database online

RESTORE DATABASE Runehold_Recovery WITH RECOVERY;

Step 5: verify

Connect. Query. Check row counts against what you expected. Pick a handful of tables and verify sample rows look right.

Step 6: document what you did and how long it took

RTO — Recovery Time Objective — is the maximum acceptable downtime. If your restore drill took 45 minutes, your RTO is 45 minutes. If leadership expects 15 minutes, you have a gap and need to work on it.

Run this drill every quarter. Rotate who leads it so multiple people know the steps.

Finding and applying the right log chain

When disaster strikes, you need to know every backup in the chain. Quick script:

-- FULL + DIFF + LOG history for a database
WITH history AS (
    SELECT bs.database_name,
           bs.type,
           bs.backup_start_date,
           bs.backup_finish_date,
           bmf.physical_device_name
    FROM msdb.dbo.backupset AS bs
    JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id
    WHERE bs.database_name = 'Runehold'
      AND bs.backup_start_date >= DATEADD(DAY, -7, GETDATE())
)
SELECT * FROM history ORDER BY backup_start_date;

Shows every backup SQL Server Agent has recorded. Handy during a disaster.

Verifying backups before disaster

RESTORE VERIFYONLY checks a backup file for basic corruption without actually restoring:

RESTORE VERIFYONLY FROM DISK = N'D:\Backups\Runehold_FULL_20260315.bak';

Fast, cheap, safe. Every backup job should run RESTORE VERIFYONLY immediately after the backup completes. Catches corrupt backups before you need them.

Also: periodically do a full test restore (the drill above) to catch issues VERIFYONLY misses. Every few months.

Backup to Azure Blob or S3

Modern SQL Server can back up directly to Azure Blob Storage (and with some tooling, S3). Off-site copies without any file-copy scripting.

BACKUP DATABASE Runehold
TO URL = N'https://runehold.blob.core.windows.net/backups/Runehold_20260315.bak'
WITH CREDENTIAL = N'AzureStorageCredential',
     COMPRESSION, CHECKSUM, FORMAT, INIT;

The CREDENTIAL is a SQL Server object holding the Azure SAS token. Set up once, use forever.

For EU companies like Runehold, off-site backups typically live in an EU-only Azure region or EU-only S3 bucket. Data-residency compliance matters.

GDPR, backups, and the “right to be forgotten”

An awkward one. When a customer exercises their GDPR right to erasure, you have to delete their data. Easy in live tables. Not easy in backups — a backup taken yesterday contains the row you’re supposed to erase today.

Standard compliance approach: document in your privacy policy that backups are retained for a defined period (often 30-90 days), and the erasure applies to live data immediately plus in backups as old backups roll off the retention window. You don’t edit old backups; you rotate them out.

Full legal deep-dive is out of scope here. Discuss with whoever owns your DPO (Data Protection Officer) function. Just be aware the intersection of backups and GDPR has documented answers; don’t invent your own.

Run this on your own machine

USE master;
GO

-- 1. Take a full backup to your disk
BACKUP DATABASE Runehold
TO DISK = N'C:\Temp\Runehold_FULL.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM, STATS = 10;

-- 2. Verify it
RESTORE VERIFYONLY FROM DISK = N'C:\Temp\Runehold_FULL.bak';

-- 3. Back up the log (requires FULL recovery)
ALTER DATABASE Runehold SET RECOVERY FULL;
BACKUP DATABASE Runehold TO DISK = N'C:\Temp\Runehold_FULL.bak' WITH FORMAT, INIT;
BACKUP LOG Runehold TO DISK = N'C:\Temp\Runehold_LOG.trn' WITH FORMAT, INIT;

-- 4. Restore into a new name (the drill)
RESTORE DATABASE Runehold_Recovery
FROM DISK = N'C:\Temp\Runehold_FULL.bak'
WITH
    MOVE N'Runehold'     TO N'C:\Temp\Runehold_R.mdf',
    MOVE N'Runehold_log' TO N'C:\Temp\Runehold_R.ldf',
    NORECOVERY, REPLACE;

RESTORE LOG Runehold_Recovery
FROM DISK = N'C:\Temp\Runehold_LOG.trn'
WITH RECOVERY;

-- 5. Verify the restored database
SELECT COUNT(*) FROM Runehold_Recovery.Sales.Customer;
SELECT COUNT(*) FROM Runehold.Sales.Customer;
-- Should match.

-- 6. Drop the test restore
DROP DATABASE Runehold_Recovery;

Run it end-to-end once. Make it muscle memory. The day you need to do this on prod, it will not be a good day, but it will be a fast one.

Next: SQL Server Agent — how to schedule the backups above, plus all the other repeatable stuff a DBA automates.

Search