Backup and Recovery in SQL Server

A SQL Server backup strategy comes down to three decisions: how much data you can afford to lose, how long you can afford to be down, and which combination of recovery model and backup types delivers that. For a database that matters, the usual answer is the FULL recovery model with three kinds of backup on a schedule:

-- Full: the baseline (for example weekly or nightly)
BACKUP DATABASE SalesDB
TO DISK = N'D:\Backup\SalesDB_full.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;

-- Differential: everything changed since the last full (for example nightly or every few hours)
BACKUP DATABASE SalesDB
TO DISK = N'D:\Backup\SalesDB_diff.bak'
WITH DIFFERENTIAL, CHECKSUM, COMPRESSION;

-- Transaction log: everything since the last log backup (for example every 15 minutes)
BACKUP LOG SalesDB
TO DISK = N'D:\Backup\SalesDB_log_1015.trn'
WITH CHECKSUM, COMPRESSION;

The rest of this page explains why, and how to know the backups will actually restore.

Start with RPO and RTO

• Recovery point objective (RPO): the maximum amount of data, measured in time, that you can lose. An RPO of 15 minutes means that after a failure you must be able to get back to a state no more than 15 minutes old.
• Recovery time objective (RTO): the maximum time the database may be unavailable while you restore it.

RPO is set by how often you back up, and in practice by how often you back up the transaction log. RTO is set by how long a restore takes: the size of the database, the speed of the storage and network, and how many backup files have to be applied.

These are business decisions. Get them agreed before choosing a schedule, because they determine everything below.

Recovery models

The recovery model is a database setting that controls how the transaction log is kept and therefore which backups are possible.

	SIMPLE	FULL	BULK_LOGGED
Log backups	Not possible	Required	Required
Point-in-time restore	No	Yes	Yes, except within a log backup that contains bulk operations
Data you can lose	Everything since the last full or differential backup	Normally nothing beyond the last log backup	As FULL, with more exposure during bulk operations
Log space reuse	Automatic after a checkpoint	Only after a log backup	Only after a log backup
Typical use	Development, test, data that can be reloaded	Production	Short periods around large bulk loads

Check and change it:

SELECT name, recovery_model_desc FROM sys.databases;

ALTER DATABASE SalesDB SET RECOVERY FULL;

Two consequences catch people out:

• FULL without log backups is the worst of both. The log is never cleared, so the log file grows until the disk is full. If log_reuse_wait_desc in sys.databases shows LOG_BACKUP, this is the cause. Either schedule log backups or switch to SIMPLE.
• Switching to FULL does nothing until the next full backup. The log chain starts at the first full (or differential) backup taken after the switch. Until then the database still behaves as if it were in SIMPLE.

New databases copy their recovery model from the model database.

Backup types

Full. A copy of the whole database plus enough of the log to make it consistent. It is the base for everything else. A full backup does not clear the transaction log.

Differential. Everything that has changed since the most recent full backup, known as the differential base. Differentials are cumulative: each one is larger than the last, and to restore you need only the full and the latest differential, not the ones in between.

Transaction log. All log records since the previous log backup. Log backups are what allow point-in-time recovery, and taking one is what lets the log file's space be reused. Available only in FULL and BULK_LOGGED.

Copy-only. A full or log backup taken WITH COPY_ONLY that leaves the regular sequence untouched. Use it for any ad hoc backup, such as a copy for a test server:

BACKUP DATABASE SalesDB
TO DISK = N'D:\Backup\SalesDB_adhoc.bak'
WITH COPY_ONLY, CHECKSUM, COMPRESSION;

Tail-log. A final log backup taken after a failure, or just before a planned restore, to capture the log records not yet backed up. It is what turns "we lost the last 12 minutes" into "we lost nothing".

File, filegroup and partial backups also exist for very large databases, where backing up everything at once is impractical.

The backup chain

Backups are only useful as a sequence. A restore to the latest possible point applies, in order:

1. The most recent full backup.
2. The most recent differential based on that full, if there is one.
3. Every log backup taken after that differential (or after the full), with none missing.
4. The tail-log backup, if you could take one.

Sun 00:00  FULL
Mon 00:00  DIFF
Tue 00:00  DIFF           <- restore step 2
Tue 00:15  LOG            <- step 3 ...
Tue 00:30  LOG
   ...
Tue 14:30  LOG
Tue 14:41  failure -> tail-log backup, if possible (step 4)

Things that break or disturb the chain:

• A missing or damaged log backup. You can restore up to the file before the gap and no further, unless a later differential or full backup bridges it. One lost file out of hundreds can cost hours of data.
• Switching to SIMPLE and back. This discards the log chain. Take a full or differential backup immediately afterwards to start a new one.
• An unplanned full backup. Any full backup that is not copy-only becomes the new differential base. If a colleague or a VM backup tool takes one and the file is later deleted, your subsequent differentials are useless. Log backups are not affected by extra full backups, which is one reason to keep log backups for longer than you think you need.

Matching a schedule to the objectives

Objective	A workable pattern
Losing a day is acceptable	SIMPLE model, nightly full backup
Losing a few hours is acceptable	SIMPLE model, nightly full plus differentials during the day
RPO of minutes	FULL model, full weekly or nightly, differential nightly or every few hours, log backup every 5 to 15 minutes

Log backup frequency sets the RPO. Differentials exist to shorten restores: without them, a failure on day six of a weekly full means replaying six days of log backups. The only way to know your real RTO is to time a restore on comparable hardware.

High availability features such as availability groups and failover clustering protect against server failure. They are not backups: a mistaken DELETE or a dropped table is faithfully replicated to every replica.

Where the backups go

• Never keep the only copy on the same disk, or the same server, as the database.
• A common rule of thumb is three copies, on two kinds of storage, with one off-site.
• SQL Server can back up directly to Azure Blob Storage with BACKUP ... TO URL, and SQL Server 2022 and later can do the same to S3-compatible object storage.
• WITH COMPRESSION is available in Enterprise, Standard and Developer editions and usually makes backups both smaller and faster, at the cost of CPU. SQL Server 2025 adds the ZSTD algorithm: WITH COMPRESSION (ALGORITHM = ZSTD).
• Backups can be encrypted with WITH ENCRYPTION, using a certificate or asymmetric key. Back up that certificate and its private key separately. The same applies to databases using transparent data encryption: without the certificate, the backup cannot be restored anywhere.
• Decide how long each backup is kept, and delete expired files with an automated job.

System databases

• master holds logins, server configuration and the list of databases. Back it up after any server-level change; only full backups are possible.
• msdb holds SQL Server Agent jobs, schedules and the backup history. Back it up regularly.
• model needs a backup only if you have customized it.
• tempdb cannot be backed up and is recreated at every startup.

Verifying backups

A backup that has never been restored is an assumption. Three levels of checking, cheapest first:

1. Checksums at backup time. WITH CHECKSUM verifies each page's checksum as it is read and writes a checksum for the backup itself. Make it the default for the instance:

EXEC sp_configure 'backup checksum default', 1;
RECONFIGURE;

2. RESTORE VERIFYONLY. Confirms the backup set is complete and readable, and rechecks the checksums:

RESTORE VERIFYONLY
FROM DISK = N'D:\Backup\SalesDB_full.bak'
WITH CHECKSUM;

It does not check the structure of the data inside, so passing it does not guarantee a usable database.

3. A real restore. On a schedule, restore the full chain to another server and run DBCC CHECKDB on the result. This is the only test that proves the backups work, and it also gives you a measured restore time to compare with your RTO.

Also monitor that backups are happening at all. The history is in msdb:

SELECT d.name,
       d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_differential,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> N'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

A NULL in last_full, or a FULL model database with an old last_log, needs attention today.

Scheduling and permissions

Schedule backups with SQL Server Agent jobs or maintenance plans. Express edition has no Agent; use Windows Task Scheduler (or cron on Linux) to run a script with sqlcmd. Azure SQL Database and Azure SQL Managed Instance take automated backups for you, and Azure SQL Database does not support the BACKUP statement at all.

BACKUP DATABASE and BACKUP LOG require membership in sysadmin, db_owner or db_backupoperator. The file is written by the SQL Server service account, not by you, so that account needs write permission on the destination folder.

Common mistakes

• Running in FULL recovery with no log backups, then shrinking the log file every time the disk fills.
• Assuming the SIMPLE model means "no backups needed". It means no log backups; full backups are still essential.
• Taking ad hoc full backups without COPY_ONLY.
• Appending every backup to one file. BACKUP adds a new backup set to an existing file by default, so the file grows without limit and one corruption loses everything. Write each backup to its own file with the date and time in the name.
• Keeping backups only on the database server.
• Never testing a restore.

Further reading

• Back up and restore of SQL Server databases
• Recovery models (SQL Server)
• BACKUP (Transact-SQL)
• Backup compression (SQL Server)