Sign in to save your place.

MODULE 11 OF 12

Backups & Restore

Full, differential, and log backups; restore a database from backup.

Backup types

Backups protect your data. Restore brings it back after failure or to copy to another server.

TypeDescription
Full backupComplete copy of the database at a point in time. Base for restore.
DifferentialAll changes since the last full backup. Restore = full + latest differential.
Log backupTransaction log backup (full recovery model). Enables point-in-time restore.

RESTORE DATABASE

Use RESTORE DATABASE to restore from a backup file. You may need to restore full, then differential, then log in order. Option WITH REPLACE overwrites an existing database.

Key Takeaway

Full backup = full snapshot. Differential = changes since last full. Log = transaction log. Restore in order: full → differential → log (if applicable).

Backup chain

Full → Differential → Log. Restore in that order to reach a point in time.

BACKUP and RESTORE examples

-- Full backup to file
BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB_Full.bak' WITH INIT;

-- Differential backup
BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB_Diff.bak' WITH DIFFERENTIAL;

-- Restore from full backup (overwrite existing DB)
RESTORE DATABASE MyDB FROM DISK = 'C:\Backups\MyDB_Full.bak' WITH REPLACE;

Practice

On a test database: run a full BACKUP DATABASE to a .bak file. Restore it to a new database name using RESTORE DATABASE ... WITH MOVE to specify file locations if needed.

Quiz

1. What does a full backup contain?

A complete copy of the database at a point in time
Only changes since the last backup
Only the transaction log
Only table structures, no data

Correct! A full backup is a complete copy of the database.

A full backup contains the entire database at the time the backup runs.

2. To overwrite an existing database when restoring, you use:

WITH REPLACE
WITH OVERWRITE
WITH INIT
WITH FORCE

Correct! RESTORE ... WITH REPLACE overwrites the existing database.

Use RESTORE DATABASE ... WITH REPLACE to overwrite an existing database.

October 2026: a backup is proven by a restore

A full backup is the whole database. A differential backup is the pages changed since the full. A log backup is the log records since the last log backup, and it requires the full or bulk-logged recovery model. SIMPLE recovery does not give you a chain of log backups, so it does not give you a point in time between full backups.

Azure SQL Database takes automated backups for you. You still need to know how to restore, including a restore to a different name so you can check the data without overwriting the live database. Microsoft Fabric is not this backup design. This lesson does not configure Fabric.

BACKUP DATABASE Practice
TO DISK = 'C:\SQLBackups\Practice.bak'
WITH INIT, CHECKSUM;

Try this. Back up a practice database to a path you can delete, drop a table you can recreate, and restore the backup over a copy. Read the table from the copy.

Knowledge check

When do you know a backup is good?