SQL Server Backup Types and Recovery Models Explained

Backups are the one part of database work you cannot get wrong. The right strategy depends on two questions your business must answer: how much data can we afford to lose (recovery point objective, RPO) and how long can we be down (recovery time objective, RTO). SQL Server's backup types and recovery models are the tools you use to meet them.

SQL Server backup types and recovery models

Recovery models

  • SIMPLE: the transaction log is truncated automatically at checkpoints, so you cannot take log backups. You can only restore to the time of a full or differential backup. Good for development, test and databases that can be rebuilt easily.
  • FULL: every change is kept in the log until a log backup. With regular log backups you can restore to any point in time. The standard choice for production. Without log backups, the log keeps growing.
  • BULK_LOGGED: like FULL, but some bulk operations are minimally logged. Point-in-time restore is not possible within a log backup that contains bulk operations. Usually switched to temporarily for big loads.
SELECT name, recovery_model_desc FROM sys.databases;

ALTER DATABASE [Payroll] SET RECOVERY FULL;

Backup types

Full backup

A complete copy of the database, including enough log to make it consistent. Every restore starts from a full backup.

BACKUP DATABASE [Payroll]
TO DISK = N'D:\Backup\Payroll_FULL.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

Differential backup

Contains every extent changed since the last full backup. Each differential grows over time until the next full. To restore, you need the full backup plus only the latest differential.

BACKUP DATABASE [Payroll]
TO DISK = N'D:\Backup\Payroll_DIFF.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

Transaction log backup

Contains all log records since the previous log backup, and lets the log space be reused. Only possible in FULL or BULK_LOGGED. The interval between log backups is roughly your maximum data loss.

BACKUP LOG [Payroll]
TO DISK = N'D:\Backup\Payroll_LOG_202610241200.trn'
WITH COMPRESSION, CHECKSUM;

Copy-only backup

A one-off backup, for example to refresh a test environment, that does not reset the differential base or affect the log chain. Use WITH COPY_ONLY so your regular schedule is not disturbed.

A typical production schedule

BackupFrequency
FullWeekly (or nightly for smaller databases)
DifferentialDaily
Transaction logEvery 5 to 15 minutes

Adjust to your RPO and RTO: more frequent log backups mean less data loss; more frequent full or differential backups mean faster restores.

Good habits

  • Use CHECKSUM so SQL Server validates pages as it backs them up.
  • Use COMPRESSION; it usually makes backups smaller and faster.
  • Keep copies off the server, ideally in another location. BACKUP ... TO URL can write directly to Azure Blob Storage.
  • Test restores regularly. RESTORE VERIFYONLY checks that a file is readable, but only a real restore, followed by DBCC CHECKDB, proves you can recover.
  • Monitor the jobs and alert when a backup is missed, not just when one fails.

Check your backup history

SELECT TOP (20)
    database_name,
    CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' END AS backup_type,
    backup_finish_date,
    CAST(compressed_backup_size / 1048576.0 AS DECIMAL(12,1)) AS size_mb
FROM msdb.dbo.backupset
ORDER BY backup_finish_date DESC;

Azure SQL Database

In Azure SQL Database, backups are automatic: full, differential and log backups are taken for you and support point-in-time restore within the configured retention period. You can add long-term retention for compliance. You still need to know your retention settings and practise a restore.

Quick checklist

  • Production databases: FULL recovery model plus regular log backups.
  • Full plus differential plus log, matched to your RPO and RTO.
  • CHECKSUM and COMPRESSION on every backup.
  • Copies stored off the server.
  • Regular test restores with DBCC CHECKDB.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more