Backup Types and the Recovery Model
SQL Server offers three core backup types: full, differential, and transaction log. A full backup captures the entire database at a point in time, including enough of the transaction log to bring the database to a consistent state on restore. A differential backup captures only the data extents that changed since the last full backup, making it faster and smaller. A transaction log backup captures all log records since the last log backup, and is only available when the database is in the Full or Bulk-Logged recovery model, not Simple.
Cricket analogy: A full backup is like recording an entire Test match from ball one, while a differential backup is like only re-recording the session after tea when Kohli came out to bat — you still need the original recording to make sense of it.
Recovery Models: Simple, Full, and Bulk-Logged
The recovery model controls how the transaction log is managed and what backup strategies are possible. Simple recovery model automatically truncates the log after each checkpoint, so you cannot take log backups and can only restore to the point of the last full or differential backup — data loss since then is unavoidable. Full recovery model retains all log records until a log backup is taken, enabling point-in-time restore and minimal data loss. Bulk-Logged is a hybrid that minimally logs certain bulk operations for performance but still supports log backups, at the cost of losing precise point-in-time restore during bulk operations.
Cricket analogy: Simple recovery is like only keeping the scorecard at the end of each innings — you can't replay a specific over, whereas Full recovery is like ball-by-ball commentary logs that let you reconstruct any exact moment, such as the 18th over of an IPL chase.
Performing and Restoring Backups
Backups are performed with the BACKUP DATABASE and BACKUP LOG T-SQL statements, typically writing to a .bak or .trn file, or directly to Azure Blob Storage. Restores use RESTORE DATABASE followed by RESTORE LOG statements in sequence, and every restore except the final one in the chain must specify WITH NORECOVERY to keep the database in a restoring state ready for the next file. The final restore uses WITH RECOVERY (the default) to roll back uncommitted transactions and bring the database online.
Cricket analogy: Restoring a database is like reconstructing an innings from the scorecard, the fall-of-wickets list, and the over-by-over log in strict order — you can't apply the 30th over before the 29th.
-- Full backup
BACKUP DATABASE [SalesDB]
TO DISK = N'D:\Backups\SalesDB_Full.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;
-- Differential backup (later that day)
BACKUP DATABASE [SalesDB]
TO DISK = N'D:\Backups\SalesDB_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;
-- Transaction log backup
BACKUP LOG [SalesDB]
TO DISK = N'D:\Backups\SalesDB_Log1.trn';
-- Restore sequence: full -> differential -> log, point-in-time
RESTORE DATABASE [SalesDB]
FROM DISK = N'D:\Backups\SalesDB_Full.bak'
WITH NORECOVERY, REPLACE;
RESTORE DATABASE [SalesDB]
FROM DISK = N'D:\Backups\SalesDB_Diff.bak'
WITH NORECOVERY;
RESTORE LOG [SalesDB]
FROM DISK = N'D:\Backups\SalesDB_Log1.trn'
WITH RECOVERY, STOPAT = '2026-07-10T14:30:00';Never leave a database in NORECOVERY state as the final step of a restore chain during a production cutover — it remains inaccessible to applications until the final RESTORE ... WITH RECOVERY is issued. Always verify the STOPAT time falls within the range covered by your log backups before running a point-in-time restore.
Use WITH CHECKSUM on backups and periodically run RESTORE VERIFYONLY or a full test restore to a scratch instance. A backup file that has never been restore-tested is not a verified recovery strategy — it's an assumption.
- Full, differential, and transaction log backups form the three core backup types in SQL Server.
- The recovery model (Simple, Full, Bulk-Logged) determines whether log backups and point-in-time restore are possible.
- Simple recovery truncates the log at checkpoints and cannot support log backups.
- Restore chains must apply full, then differential (optional), then log backups in strict chronological order.
- All restores except the last must use WITH NORECOVERY; the final restore uses WITH RECOVERY to bring the database online.
- RESTORE ... WITH STOPAT enables point-in-time recovery within the range covered by available log backups.
- Always test restores and use WITH CHECKSUM to catch corrupt backup files before you need them in an emergency.
Practice what you learned
1. Which recovery model does NOT support transaction log backups?
2. In a restore sequence of full, differential, and log backups, which clause must be used on every restore step except the last?
3. What does a differential backup capture?
4. Which T-SQL clause enables point-in-time restore to a specific timestamp?
5. Why should you periodically run RESTORE VERIFYONLY or a full test restore?
Was this page helpful?
You May Also Like
High Availability Basics
Core SQL Server high-availability options — Always On Availability Groups, Failover Cluster Instances, and log shipping — and how they differ.
Security and Permissions
The principal-securable-permission model in SQL Server, from logins and users to roles and the principle of least privilege.
SQL Server Agent Jobs
How SQL Server Agent automates scheduled tasks, jobs, steps, schedules, alerts, and operator notifications.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics