Backup & Restore

Why Backups Matter and the Three Backup Types

A backup is a point-in-time copy of your database that can be used to recover data after hardware failure, accidental deletion, corruption, or disaster. SQL Server supports three backup types that work together as layers in a complete protection strategy — they are not alternatives, they are used in combination. To restore, you always apply them in order: full first, then differential, then log backups up to the target recovery point.

1. Full Backup
A full backup captures the entire database — every data page, every object, every row — at one specific point in time. It is the foundation that every restore chain must start from. No differential or transaction log backup can be applied without a full backup first. Full backups are the largest and slowest to create, which is why most production strategies take them once per week rather than daily. The backup file contains everything needed to restore the database to the exact moment the backup was taken — nothing more is required if you only need to recover to that point.

2. Differential Backup
A differential backup captures only the data pages that have changed since the last full backup — not since the last differential. This means each successive differential grows slightly as more pages change, but it is always smaller and faster than taking another full backup. A typical strategy takes differential backups once per day. During a restore, you apply only the most recent differential — older differentials are skipped because the latest one already includes all their changes. If no differential exists, you go straight from the full backup to the log backups.

3. Transaction Log Backup
A transaction log backup captures every committed transaction that occurred since the last log backup. It is the smallest and fastest backup to take, which is why production systems run log backups every 15 to 30 minutes. Log backups enable point-in-time recovery — the ability to restore the database to any specific minute within the backup chain, not just to the end of a file. This is essential when recovering from accidental data changes: if a developer deleted rows at 14:23, you can restore to 14:22 exactly. Log backups require the database to be in FULL or BULK_LOGGED recovery model — the SIMPLE recovery model truncates the log automatically and makes log backups impossible.

• A full backup is required before any differential or log backup can be taken
• A differential backup contains all changes since the last full — not since the last differential
• A transaction log backup requires the database to be in FULL or BULK_LOGGED recovery model
• The SIMPLE recovery model truncates the log automatically — no log backups are possible
• Backups do not lock the database — users can continue reading and writing during a backup
• A backup file is useless unless you have also tested that it can be successfully restored

-- Check the current recovery model of the database
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'DataplexaStore';
-- Switch to FULL recovery model to enable transaction log backups
ALTER DATABASE DataplexaStore SET RECOVERY FULL;
-- FULL BACKUP: capture the entire database to a file
BACKUP DATABASE DataplexaStore
TO DISK = 'C:\Backups\DataplexaStore_Full_20240701.bak'
WITH
    NAME        = 'DataplexaStore Full Backup 2024-07-01',
    DESCRIPTION = 'Weekly full backup',
    COMPRESSION,        -- reduce backup file size
    CHECKSUM,           -- write a checksum to detect corruption later
    STATS = 10;         -- print progress every 10 percent

• COMPRESSION reduces the backup file size significantly — typically 60–80% smaller — at the cost of slightly more CPU during the backup
• CHECKSUM calculates and stores a checksum in the backup file — SQL Server verifies it during restore to detect corruption in the backup file itself
• STATS = 10 prints a progress message every 10% — useful for long backups on large databases
• The backup path must exist on the SQL Server machine — not the client machine running the query

Differential and Transaction Log Backups

After the weekly full backup is in place, differential and log backups narrow the gap between the last full and the current moment. A differential taken daily means the worst-case restore effort is replaying at most one day of log backups. Log backups taken every 15 minutes mean the maximum data loss in a failure is 15 minutes of transactions. Choosing the right intervals is a balance between recovery time objectives, storage costs, and the performance impact of frequent backup jobs.

-- DIFFERENTIAL BACKUP: only pages changed since the last full backup
BACKUP DATABASE DataplexaStore
TO DISK = 'C:\Backups\DataplexaStore_Diff_20240702.bak'
WITH
    DIFFERENTIAL,       -- marks this as a differential backup
    NAME        = 'DataplexaStore Differential Backup 2024-07-02',
    COMPRESSION,
    CHECKSUM,
    STATS = 25;
-- TRANSACTION LOG BACKUP: all committed transactions since the last log backup
-- Must be on FULL or BULK_LOGGED recovery model
BACKUP LOG DataplexaStore
TO DISK = 'C:\Backups\DataplexaStore_Log_20240702_1400.bak'
WITH
    NAME        = 'DataplexaStore Log Backup 2024-07-02 14:00',
    COMPRESSION,
    CHECKSUM;
-- COPY_ONLY backup: a standalone full backup that does not interrupt
-- the differential chain -- use for ad-hoc copies without affecting the schedule
BACKUP DATABASE DataplexaStore
TO DISK = 'C:\Backups\DataplexaStore_CopyOnly_20240702.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;

• The differential backup processed only 184 pages versus 1248 for the full — it is much faster to take and smaller to store
• BACKUP LOG is the T-SQL statement for transaction log backups — it uses the database name, not a WITH LOG option
• COPY_ONLY creates a standalone full backup that does not reset the differential base point — the regular differential chain continues uninterrupted
• Use COPY_ONLY when handing a copy of the database to a developer or tester without disturbing the production backup schedule

Restoring a Database

Restore is the reverse of backup — but the order and options matter critically. A restore always starts with the full backup. If a differential exists, apply the most recent differential next. Then apply every transaction log backup in chronological order up to the target point in time. Each backup in the chain except the last must be restored with WITH NORECOVERY, which leaves the database in a restoring state ready to accept the next piece. The final backup in the chain is restored with WITH RECOVERY, which brings the database online.

-- Full restore sequence: full → differential → log backups.
-- Every step except the last uses WITH NORECOVERY.
-- Step 1: restore the full backup -- NORECOVERY leaves it open for more
RESTORE DATABASE DataplexaStore
FROM DISK = 'C:\Backups\DataplexaStore_Full_20240701.bak'
WITH
    NORECOVERY,             -- keep in restoring state -- more backups to come
    REPLACE,                -- overwrite the existing database if it exists
    MOVE 'DataplexaStore'     TO 'C:\Data\DataplexaStore.mdf',
    MOVE 'DataplexaStore_log' TO 'C:\Data\DataplexaStore_log.ldf',
    STATS = 10;
-- Step 2: apply the most recent differential backup
RESTORE DATABASE DataplexaStore
FROM DISK = 'C:\Backups\DataplexaStore_Diff_20240702.bak'
WITH NORECOVERY, STATS = 10;
-- Step 3: apply transaction log backups in order -- NORECOVERY on each
RESTORE LOG DataplexaStore
FROM DISK = 'C:\Backups\DataplexaStore_Log_20240702_1200.bak'
WITH NORECOVERY;
-- Step 4: final log backup -- WITH RECOVERY brings the database online
RESTORE LOG DataplexaStore
FROM DISK = 'C:\Backups\DataplexaStore_Log_20240702_1400.bak'
WITH RECOVERY;              -- database is now online and accessible

• WITH NORECOVERY on every step except the last is the most critical detail in a restore — applying RECOVERY too early closes the chain and prevents further log restores
• WITH REPLACE overwrites the existing database files — required when restoring over a live database
• MOVE ... TO ... redirects data and log file paths — essential when restoring to a different server or drive layout
• If you apply RECOVERY too early by mistake, you must restart the entire restore chain from the full backup

Point-in-Time Restore

Transaction log backups enable recovery to any specific moment within the backup chain — not just to the end of a log file. If a developer accidentally deleted all rows from the Orders table at 14:23, you can restore the database to 14:22 and recover the data. The STOPAT option on the final log restore tells SQL Server exactly where to stop replaying transactions. All transactions after that point are discarded. This is why transaction log backups are the foundation of any serious recovery strategy.

-- Point-in-time restore: recover to exactly 14:22 on 2024-07-02.
-- The accidental DELETE happened at 14:23 -- stop one minute before.
-- Steps 1-3: same as before (full + differential + earlier logs with NORECOVERY)
-- Final log: use STOPAT to halt recovery at the target moment
RESTORE LOG DataplexaStore
FROM DISK = 'C:\Backups\DataplexaStore_Log_20240702_1400.bak'
WITH
    RECOVERY,
    STOPAT = '2024-07-02T14:22:00';  -- stop replaying here -- 1 minute before deletion
-- Verify the backup history for the database using the msdb catalogue
SELECT
    bs.database_name,
    bs.type                                              AS BackupType,
    bs.backup_start_date,
    bs.backup_finish_date,
    CAST(bs.backup_size / 1048576.0 AS DECIMAL(10,2))   AS SizeMB,
    bmf.physical_device_name                             AS BackupFile
FROM msdb.dbo.backupset         bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'DataplexaStore'
ORDER BY bs.backup_start_date DESC;

• STOPAT accepts a datetime string — SQL Server replays every committed transaction up to that exact moment and stops
• BackupType codes: D = full database, I = differential, L = transaction log
• msdb.dbo.backupset is the authoritative catalogue of every backup SQL Server has recorded — query it to find the exact files needed before starting a restore
• Point-in-time recovery is only possible with the FULL recovery model — the SIMPLE model cannot support it

Verifying Backups

Creating a backup file is not enough — you must verify it can actually be restored. A backup file that passes a verify check is readable and internally consistent. A restore test on a separate server confirms the data is actually there and usable. Many organisations discover their backups are corrupt or incomplete only when they need them most. RESTORE VERIFYONLY checks the backup file without restoring the database — it is a fast, non-destructive way to confirm a backup is valid after it is written.

-- Verify a backup file is readable and internally consistent
-- without actually restoring the database
RESTORE VERIFYONLY
FROM DISK = 'C:\Backups\DataplexaStore_Full_20240701.bak'
WITH CHECKSUM;      -- verify the checksum written during the backup
-- View the contents of a backup file (header and file list)
RESTORE HEADERONLY
FROM DISK = 'C:\Backups\DataplexaStore_Full_20240701.bak';
RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\DataplexaStore_Full_20240701.bak';

• RESTORE VERIFYONLY is fast and non-destructive — run it immediately after every backup job completes as a minimum sanity check
• RESTORE HEADERONLY shows the backup set metadata — backup type, dates, database name, SQL Server version — useful before starting a restore to confirm you have the right file
• RESTORE FILELISTONLY shows the logical and physical file names inside the backup — use it to get the exact names needed for the MOVE ... TO ... options in a restore script
• A checksum verify (WITH CHECKSUM) catches bit-rot and transmission errors in the backup file — always write and verify checksums on production backups

Lesson Summary

Practice Questions

Quiz

Next up — SQL Server Monitoring — How to track performance, detect blocking, identify long-running queries, and use DMVs to keep your database healthy.