Overview
Backup types, restore strategy, and recovery objectives define how a database can survive failure, human error, corruption, deployment mistakes, ransomware, hardware loss, and regional outages. A backup is only useful if it can be restored to a known good point that meets the business need.
This topic matters because database durability is not the same as recoverability. SQL Server can make committed transactions durable on disk, but that does not protect against a user dropping a table, a storage failure, a bad release, or a site outage. A real recovery strategy defines what backups are taken, how often they are taken, where they are stored, how they are secured, how they are tested, and how long a restore is expected to take.
For interviews, this topic tests whether you think operationally. Strong candidates can explain full, differential, log, copy-only, file, and tail-log backups; recovery models; restore chains; point-in-time recovery; RPO; RTO; restore testing; backup encryption; and why "we take backups" is not enough.
Core Concepts
Backup, Restore, And Recovery
A backup is a copy of database data or log records that can be used after failure. Restore is the process of copying backup data back into a database. Recovery is the phase that brings the restored database to a transactionally consistent state.
Those words matter because a restore can involve several steps:
- Restore a full backup.
- Restore the latest differential backup.
- Restore transaction log backups in order.
- Recover the database so it becomes usable.
If the database is recovered too early, additional log backups cannot be applied.
Recovery Objectives
Recovery objectives translate business risk into technical targets.
Example:
- RPO of 15 minutes means the business accepts losing at most about 15 minutes of committed work.
- RTO of 1 hour means the restore process must bring service back within about an hour.
Backup frequency affects RPO. Restore complexity, database size, storage speed, automation, and practice affect RTO.
Recovery Models
SQL Server recovery models control transaction log behavior and restore options.
The full recovery model alone does not protect data. You must also take transaction log backups.
Full Backups
A full database backup contains the whole database at the time the backup completes, plus enough log to recover that backup to a consistent state.
BACKUP DATABASE SalesDb
TO DISK = 'D:\Backups\SalesDb_full.bak'
WITH COMPRESSION, CHECKSUM;
Full backups are the base for differential backups and log restore chains. They are usually scheduled during lower-traffic windows when possible, but SQL Server backups can run while the database is online.
Differential Backups
A differential backup contains data changed since the most recent full backup that serves as the differential base.
BACKUP DATABASE SalesDb
TO DISK = 'D:\Backups\SalesDb_diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
Differential backups can reduce restore time because you restore one full backup, the latest useful differential backup, and then the needed log backups after that.
Transaction Log Backups
A transaction log backup captures log records not already backed up in the previous log backup. Log backups are available in full and bulk-logged recovery models.
BACKUP LOG SalesDb
TO DISK = 'D:\Backups\SalesDb_log.trn'
WITH COMPRESSION, CHECKSUM;
Log backups support point-in-time recovery and limit data loss. They also allow SQL Server to truncate inactive portions of the log so the log file does not grow indefinitely under full recovery.
Copy-Only Backups
A copy-only backup is an independent backup that does not disrupt the normal backup sequence.
BACKUP DATABASE SalesDb
TO DISK = 'D:\Backups\SalesDb_copyonly.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM;
Use copy-only backups for ad hoc copies, testing, refreshes, or one-off operations where you do not want to affect the differential base or normal backup plan.
Tail-Log Backups
A tail-log backup captures log records that have not yet been backed up after a failure or before a restore operation. It is used to minimize data loss when the database and log are still accessible.
BACKUP LOG SalesDb
TO DISK = 'D:\Backups\SalesDb_tail.trn'
WITH NORECOVERY, CHECKSUM;
Tail-log backup is often part of a restore process after accidental damage, before restoring over the existing database.
File And Filegroup Backups
Large databases can use file and filegroup backups to back up and restore parts of a database. This is useful when a database is too large for convenient full backups or when different filegroups have different change rates.
For most interview scenarios, know that file/filegroup strategies add complexity and should be justified by size, restore objectives, and operational maturity.
Restore Chains
A restore chain is the ordered set of backups needed to reach a target recovery point.
Common sequence:
RESTORE DATABASE SalesDb
FROM DISK = 'D:\Backups\SalesDb_full.bak'
WITH NORECOVERY;
RESTORE DATABASE SalesDb
FROM DISK = 'D:\Backups\SalesDb_diff.bak'
WITH NORECOVERY;
RESTORE LOG SalesDb
FROM DISK = 'D:\Backups\SalesDb_log_001.trn'
WITH NORECOVERY;
RESTORE LOG SalesDb
FROM DISK = 'D:\Backups\SalesDb_log_002.trn'
WITH RECOVERY;
NORECOVERY keeps the database ready for more restore steps. RECOVERY brings it online.
Point-In-Time Restore
Point-in-time restore uses transaction log backups to restore to a specific moment before a failure or mistake.
RESTORE LOG SalesDb
FROM DISK = 'D:\Backups\SalesDb_log_002.trn'
WITH STOPAT = '2026-06-20T10:14:00', RECOVERY;
This is useful after accidental data changes, such as a bad update or dropped table. You need a valid restore chain that spans the target time.
Testing Restores
A backup strategy is not proven until restore is tested. Restore testing should verify:
- Backup files are readable.
- The restore order works.
- RPO and RTO are realistic.
- DBCC checks pass on restored data.
- Application connectivity works.
- Security and permissions are correct.
- Runbooks are accurate.
RESTORE VERIFYONLY and backup checksums are useful, but they are not substitutes for real restore tests.
Backup Security
Backups contain sensitive data. They need security controls similar to production databases.
Important controls include:
- Store backups separately from database files.
- Keep off-site or cross-region copies.
- Encrypt backups where appropriate.
- Restrict backup and restore permissions.
- Protect backup storage accounts or file shares.
- Monitor backup job failures.
- Avoid restoring untrusted backup files.
- Define retention and secure deletion policy.
Backups can become a data exfiltration path if they are easier to access than the live database.
Strategy Examples
An OLTP production system with 15-minute RPO might use:
- Weekly full backups.
- Frequent differential backups.
- Transaction log backups every 5 to 15 minutes.
- Encrypted off-server storage.
- Automated restore tests.
- Documented incident runbook.
A small dev database might use:
- Simple recovery model.
- Nightly full backup.
- No point-in-time recovery.
- Short retention.
The correct plan follows the business need.
Common Mistakes
Common mistakes include:
- Taking backups but never testing restores.
- Using full recovery without log backups.
- Storing backups on the same disk as database files.
- Not encrypting backups that contain sensitive data.
- Assuming a high-availability replica replaces backups.
- Losing the restore chain by missing log backups.
- Not documenting restore steps.
- Letting backup jobs fail silently.
- Setting RPO and RTO without testing whether they are achievable.
Best Practices
Best practices include:
- Define RPO and RTO for each database.
- Choose recovery model based on business requirements.
- Use full, differential, and log backups intentionally.
- Store backups separately from database files.
- Encrypt and restrict backup access.
- Monitor backup job success and restore test success.
- Document restore runbooks.
- Test point-in-time restore.
- Keep retention aligned with legal, business, and storage requirements.
- Remember that backups and HA/DR solve different problems.