LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 3918 min read

Backup & Restore

Learn BACKUP DATABASE and RESTORE DATABASE in SQL Server, and the conceptual difference between full and differential backups.

Introduction

No amount of careful T-SQL prevents every disaster - hardware fails, someone runs an UPDATE without a WHERE clause, or an entire data center has a bad day. Backups are the safety net that makes all of that recoverable. This lesson covers how SQL Server backups work conceptually, the BACKUP DATABASE and RESTORE DATABASE statements, and the difference between full and differential backups.

What You Will Learn
  • Why regular backups are non-negotiable.
  • How to take a full backup with BACKUP DATABASE.
  • How differential backups build on a full backup.
  • How transaction log backups fit in.
  • How to restore a database with RESTORE DATABASE.

Why Backups Matter

A backup is a point-in-time copy of a database that can be restored later, undoing accidental data loss, hardware failure, or a bad deployment. Every production database needs a backup strategy, and understanding the tradeoffs between backup types helps you balance backup time, storage space, and how much data you could lose in the worst case (your recovery point objective).

Full Backups

A full backup copies the entire database - all data, indexes, and enough of the transaction log to bring it to a consistent state on restore. It is the foundation every other backup type builds on.

BACKUP DATABASE SalesDB
TO DISK = 'D:\Backups\SalesDB_Full.bak'
WITH INIT, COMPRESSION;
Result (Messages tab)

Click Run to see what this code prints.

WITH COMPRESSION

WITH COMPRESSION shrinks the backup file and usually speeds up the backup itself since less data has to be written to disk. It is supported in most modern SQL Server editions and is generally worth enabling.

Differential Backups

A differential backup captures only the data pages that changed since the last full backup - not since the last differential. This makes differential backups smaller and faster than a full backup, at the cost of needing the original full backup (plus this one differential) to restore.

BACKUP DATABASE SalesDB
TO DISK = 'D:\Backups\SalesDB_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;
Backup TypeCapturesRestore Needs
FullEverything in the databaseJust the full backup
DifferentialEverything changed since the last fullThe last full backup + this differential
Transaction logEvery logged transaction since the last log backupFull (+ differential) + every log backup since, in order

Transaction Log Backups

In the FULL recovery model, transaction log backups capture every committed transaction since the previous log backup, which is what makes point-in-time restores possible - for example, restoring a database to exactly 2:14 PM, moments before an accidental DELETE.

BACKUP LOG SalesDB
TO DISK = 'D:\Backups\SalesDB_Log.trn';

Restoring a Database

RESTORE DATABASE reverses the process. If you only have a full backup, restoring it is straightforward. If you also have a differential and/or log backups, they must be restored in order, with WITH NORECOVERY on every step except the last, which uses WITH RECOVERY to bring the database fully online.

-- Step 1: restore the full backup, leaving the database in a restoring state
RESTORE DATABASE SalesDB
FROM DISK = 'D:\Backups\SalesDB_Full.bak'
WITH NORECOVERY, REPLACE;


-- Step 2: apply the differential backup, still not recovered
RESTORE DATABASE SalesDB
FROM DISK = 'D:\Backups\SalesDB_Diff.bak'
WITH NORECOVERY;


-- Step 3: apply the log backup and bring the database online
RESTORE LOG SalesDB
FROM DISK = 'D:\Backups\SalesDB_Log.trn'
WITH RECOVERY;
NORECOVERY vs RECOVERY

WITH NORECOVERY leaves the database unable to be used yet, waiting for more backups to be applied. WITH RECOVERY finalizes the restore and brings the database online - use it only on the very last step in the restore sequence.

Recovery Models

A database's recovery model controls how much transaction log history is kept and what kinds of backups are possible.

Recovery ModelBehavior
SimpleLog space is reclaimed automatically; no transaction log backups possible, only full/differential
FullLog is kept until backed up; supports point-in-time restore via log backups
Bulk-LoggedLike Full, but minimally logs certain bulk operations for performance

Common Mistakes

Avoid These Mistakes
  • Taking backups but never testing a restore, discovering problems only during a real emergency.
  • Storing backups on the same disk (or server) as the database itself, so a hardware failure destroys both.
  • Leaving a database in FULL recovery model without ever taking log backups, causing the transaction log to grow unbounded.
  • Applying RECOVERY too early in a multi-step restore, which prevents any further backups from being applied.
  • Assuming a differential backup alone is enough to restore, forgetting it depends on the original full backup.

Best Practices

  • Follow the 3-2-1 rule: at least 3 copies of your data, on 2 different media, with 1 copy off-site.
  • Schedule regular full backups, with differentials and log backups in between based on your recovery point objective.
  • Regularly test restoring backups to a separate environment to confirm they actually work.
  • Use WITH COMPRESSION to save storage and backup time where supported.
  • Match the recovery model to your actual needs - FULL if you need point-in-time restore, SIMPLE if you do not.

Frequently Asked Questions

It depends on how much data loss is acceptable. Many production systems take nightly full backups, periodic differentials, and log backups every few minutes to minimize potential data loss.

Yes, backup files (.bak) are portable and can be copied to another server and restored there, which is also a common way to refresh a test environment with production data.

It is restoring a FULL-recovery-model database to a specific moment (not just the time of the last backup) by applying transaction log backups up to that exact point, useful for undoing a mistake that happened partway through the day.

Differentials speed up recovery by reducing how many log backups need to be replayed. They are a tradeoff between backup frequency/size and how long a full restore sequence takes.

Key Takeaways

  • BACKUP DATABASE takes full and differential backups; BACKUP LOG takes transaction log backups.
  • A full backup is the foundation every differential and log backup depends on.
  • RESTORE DATABASE/RESTORE LOG replay backups in order, using NORECOVERY until the final RECOVERY step.
  • The recovery model (Simple, Full, Bulk-Logged) determines what backup types are possible.
  • Backups are only useful if they are tested - untested backups are not a real safety net.

Summary

A solid backup and restore strategy is what turns a disaster into an inconvenience. Next, you will look at performance tuning - reading execution plans, spotting missing indexes, and measuring query cost with SET STATISTICS IO.

Next Lesson →

Performance Tuning & Execution Plans