Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 39 ยท Administration and Security

Backup, Restore and Recovery Models

Protect SQL Server data with full, differential and transaction log backups, choose a recovery model, restore to a point in time, verify backups and plan RPO and RTO.

Every database administrator is judged by one question: can you get the data back? Hardware fails, people run DELETE without WHERE, ransomware happens. A backup you have never restored is a hope, not a backup. This lesson covers SQL Server's backup types, the recovery models that decide what is possible, how to restore, and how to turn business needs into a plan.

Backups can't run in the browser, so the T-SQL is read-only, and a runnable stand-in at the end shows the "restore to a point in time" idea with a change log. For a portable overview see backup and maintenance.

What is in a database

A SQL Server database consists of at least two kinds of files:

  • Data files (.mdf, .ndf) with tables and indexes.
  • A transaction log (.ldf) that records every change before it is applied (write-ahead logging). It is what makes transactions durable and is the key to point-in-time recovery.

The three recovery models

The recovery model controls how the log is used:

Model Log behavior Restore options Typical use
SIMPLE Log space is reused after a checkpoint; no log backups Only to the time of the last full or differential backup Development, data that can be reloaded
FULL Log keeps everything until you back it up Any point in time, if you have log backups Production OLTP
BULK_LOGGED Like FULL, but bulk operations are minimally logged Point in time except inside a bulk operation Temporary, around big loads
SQL
ALTER DATABASE Shop SET RECOVERY FULL;
SELECT name, recovery_model_desc FROM sys.databases;

A very common incident: a database in FULL recovery with no log backups. The log grows until the disk is full. Either take log backups regularly or switch to SIMPLE if you don't need point-in-time restore.

Backup types

SQL
-- Full: the whole database (baseline)
BACKUP DATABASE Shop
TO DISK = N'D:\Backups\Shop_full.bak'
WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;

-- Differential: everything changed since the last full backup
BACKUP DATABASE Shop
TO DISK = N'D:\Backups\Shop_diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- Transaction log: the log since the last log backup (FULL or BULK_LOGGED only)
BACKUP LOG Shop
TO DISK = N'D:\Backups\Shop_log_0900.trn'
WITH COMPRESSION, CHECKSUM;
  • Use COMPRESSION (smaller and usually faster) and CHECKSUM (detects corruption while writing).
  • COPY_ONLY makes a one-off full backup without disturbing the regular differential chain.
  • A tail-log backup (WITH NORECOVERY) saves the last transactions of a damaged database before you restore.

A typical schedule: a full backup weekly, a differential nightly, and log backups every 5 to 15 minutes.

flowchart LR
  F["Sun: Full"] --> D1["Mon: Diff"]
  D1 --> D2["Tue: Diff"]
  D2 --> L["Log backups every 15 min"]
  L --> X["Disaster at 14:07"]

To restore: the full backup, the latest differential, then each log backup in order.

Restoring

A restore is a sequence. Every step except the last uses NORECOVERY, which leaves the database in the "restoring" state so more backups can be applied:

SQL
RESTORE DATABASE Shop FROM DISK = N'D:\Backups\Shop_full.bak' WITH NORECOVERY, REPLACE;
RESTORE DATABASE Shop FROM DISK = N'D:\Backups\Shop_diff.bak' WITH NORECOVERY;
RESTORE LOG Shop FROM DISK = N'D:\Backups\Shop_log_0900.trn' WITH NORECOVERY;
RESTORE LOG Shop FROM DISK = N'D:\Backups\Shop_log_0915.trn'
        WITH STOPAT = N'2026-03-12T09:12:30', RECOVERY;   -- bring online

STOPAT rolls forward only up to a moment, for instance just before someone's accidental DELETE. WITH RECOVERY (the default) finishes the restore and opens the database; after that you can't apply more backups.

To restore next to the original (for example to pull back one table), restore under a new name with MOVE for each file:

SQL
RESTORE DATABASE Shop_Recovered
FROM DISK = N'D:\Backups\Shop_full.bak'
WITH MOVE N'Shop'     TO N'D:\Data\Shop_Recovered.mdf',
     MOVE N'Shop_log' TO N'D:\Data\Shop_Recovered.ldf',
     RECOVERY;

Inspect a backup before restoring with RESTORE HEADERONLY, RESTORE FILELISTONLY and RESTORE VERIFYONLY. Verify is only a basic readability check; the real test is a trial restore on another server.

RPO, RTO and the 3-2-1 rule

Backups exist to meet two business numbers:

  • RPO (recovery point objective): how much data you may lose. An RPO of 15 minutes means log backups at least every 15 minutes.
  • RTO (recovery time objective): how long you may be down. A 4 TB restore takes hours; if the RTO is minutes, you also need high availability (Always On, failover clusters, log shipping).

Follow 3-2-1: three copies of the data, on two kinds of media, one off-site (cloud storage with BACKUP TO URL works). Test restores on a schedule and keep the master and msdb system databases in the plan: msdb holds your job history and backup history.

Also check for corruption

Run DBCC CHECKDB (Shop) WITH NO_INFOMSGS; regularly, ideally weekly, so that you discover damage while you still have good backups. Backups made with CHECKSUM carry page checksums, but they don't prove the database is logically healthy.

A stand-in: recovering to a point in time

The same principle, a base copy plus an ordered change log replayed up to a chosen moment, can be shown with two tables:

SQL Replaying a change log up to a point in time

Press Run (or Ctrl + Enter) to see the result here.

Change 09:10 to 09:30 and you replay the whole log, mistake included. That is exactly the choice STOPAT gives you.

SQL Which backups are needed?

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which recovery model allows restoring to any point in time?

    FULL keeps all log records until they are backed up, so you can replay up to a chosen time with STOPAT.
  2. 2. A database in FULL recovery has a transaction log that keeps growing. What is the most likely cause?

    In FULL recovery the log is only reusable after a log backup.
  3. 3. What does a differential backup contain?

    A differential is relative to the most recent full backup; you need only the latest one in the restore chain.
  4. 4. Why use WITH NORECOVERY on all but the last restore step?

    RECOVERY runs the final undo phase and opens the database; no more backups can be applied afterwards.
  5. 5. Which statement describes RPO?

    RPO is the recovery point objective: the maximum acceptable data loss, which sets the log-backup interval.

Summary

  • Pick the recovery model first: SIMPLE for rebuildable data, FULL for production with point-in-time restore.
  • Combine full, differential and log backups; use COMPRESSION and CHECKSUM.
  • Restore with NORECOVERY until the last step; STOPAT rewinds to just before a mistake.
  • Define RPO and RTO, follow 3-2-1, run DBCC CHECKDB and test your restores.

Next: Logins, users and permissions