A call comes from accounting: “Someone ran a wrong update at 14:20, can we go back to that moment?” If all you have is a VM image taken overnight, the answer is “we can go back to last night,” and that is often not acceptable. An image backup covers total loss of the server, not a return to a specific moment. Below we explain choosing the recovery model, how full, differential and transaction log backups work together, point-in-time restore, why the log file grows, and where image backups and native SQL backups collide, with T-SQL examples.
Choosing the recovery model
The recovery model determines how the transaction log is managed and which restore options are possible. The wrong model means the wrong RPO.
| Model | Log backup | Point-in-time restore | Typical use |
|---|---|---|---|
| SIMPLE | None; the log is reused automatically at checkpoint | No, only the last full/differential backup | Test, reporting, reproducible data |
| FULL | Required; the log is reused only after a log backup | Yes | Production OLTP databases |
| BULK_LOGGED | Required; bulk operations are minimally logged | No, for a log backup containing bulk operations | Temporarily, during large data load windows |
The decision rule is simple: if the database’s RPO is shorter than the full or differential backup interval, the FULL model is needed. If a one-day RPO is acceptable, staying in the SIMPLE model frees you from the log management burden. The common picture is the opposite: the model is FULL, but no log backup job was ever set up. To check and change the model:
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;
ALTER DATABASE [Sales] SET RECOVERY FULL;
Switching from SIMPLE to FULL doesn’t start the log chain right away. The chain starts with the first full (or differential) backup taken after the switch; until that backup is taken, the database effectively behaves like SIMPLE. Take a full backup right after changing the model.
Backup types and the chain
- Full backup: Contains the whole database and the part of the log needed for a consistent restore. It is the starting point for the other backups.
- Differential backup: Contains the extents changed since the last full backup. It is cumulative: only the latest differential backup is needed in a restore.
- Transaction log backup: Contains the log records written since the previous log backup. Log backups are applied in sequence. If a log backup is missing, a restore starting from the same full or differential backup can go to any point up to the end of the last log backup before the missing one; the range covered by the missing backup and everything after it can’t be restored with this chain. If a full or differential backup was taken after the gap, the restore is started from that backup, and the log backups after it can be used again. What is lost are the recovery points between the start of the missing log backup and the next full or differential backup.
- COPY_ONLY backup: An independent backup that doesn’t affect the chain’s sequence. A copy-only full backup doesn’t change the differential base; a copy-only log backup doesn’t truncate the log.
Differential backups are always taken relative to the last normal full backup. A full backup that isn’t copy-only, taken by another tool, silently changes the base of your differential backups; nobody notices until restore day. We’ll come back to this in the image backup section.
Example backup plan
A typical plan for a production database with an RPO of 15 minutes: a full backup once a week, a differential every night and a log backup every 15 minutes. Backups should sit in a separate repository, not on the server’s local disk. A backup lost together with the server isn’t a backup.
-- Sunday 01:00 full backup
BACKUP DATABASE [Sales]
TO DISK = N'\\backup01\sql\Sales\Sales_FULL_20261108.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;
-- Monday–Saturday 01:00 differential backup
BACKUP DATABASE [Sales]
TO DISK = N'\\backup01\sql\Sales\Sales_DIFF_20261110.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
-- Log backup every 15 minutes
BACKUP LOG [Sales]
TO DISK = N'\\backup01\sql\Sales\Sales_LOG_202611101415.trn'
WITH COMPRESSION, CHECKSUM;
You can generate file names with the date and time in the SQL Server Agent job step, or use widely used maintenance scripts. Two things are essential: each backup is written to a separate file, and CHECKSUM is used. With CHECKSUM, SQL Server verifies page checksums during the backup and adds its own checksum to the backup. A common failure in the first setup is permissions: if the SQL Server service account running the job doesn’t have write permission on the target share, the backup fails with “Operating system error 5 (Access is denied).” To calculate the plan’s impact on backup storage size, the method in the backup storage capacity calculation article can be used.
Restoring to a specific point in time
Scenario: a faulty UPDATE was run on 10 November at 14:22 and noticed about an hour later. The first reflex is to restore over the production database. Don’t. Restore the data under a separate name to 14:21 and fix the affected records by comparison; that way, correct records entered in the hour in between are preserved.
- First take the last log backup; this brings the log records covering the moment of the incident into the chain.
- Restore the last full backup with
NORECOVERYto a new database name. - Apply the last differential backup with
NORECOVERY. - Apply the log backups after the differential backup in sequence; use
STOPATon the log backup that contains the moment of the incident. - Bring the database online with
RECOVERYand compare the data.
RESTORE DATABASE [Sales_1421]
FROM DISK = N'\\backup01\sql\Sales\Sales_FULL_20261108.bak'
WITH MOVE N'Sales' TO N'D:\Data\Sales_1421.mdf',
MOVE N'Sales_log' TO N'L:\Log\Sales_1421.ldf',
NORECOVERY, STATS = 10;
RESTORE DATABASE [Sales_1421]
FROM DISK = N'\\backup01\sql\Sales\Sales_DIFF_20261110.bak'
WITH NORECOVERY;
-- log backups after 01:00 in sequence...
RESTORE LOG [Sales_1421]
FROM DISK = N'\\backup01\sql\Sales\Sales_LOG_202611101430.trn'
WITH STOPAT = '2026-11-10T14:21:00', NORECOVERY;
RESTORE DATABASE [Sales_1421] WITH RECOVERY;
Find the logical file names to use with MOVE using RESTORE FILELISTONLY. If the whole database is to be returned to the moment of the incident (for example ransomware or serious corruption), the last log backup is taken with BACKUP LOG ... WITH NORECOVERY; this “tail-log” backup puts the database into the restoring state and prevents the latest transactions from being lost. If the data files are damaged, the NO_TRUNCATE option is added. If no tail-log backup is taken, the transactions of the last minutes are lost.
Why the log file grows
When the “L: drive is full” alert comes at midnight, the cause is usually the same: no log backups are being taken in the FULL model. But it isn’t the only cause. Don’t guess; SQL Server already tells you in the log_reuse_wait_desc column why the log can’t be reused:
| Value | Meaning | What to do |
|---|---|---|
| LOG_BACKUP | Waiting for a log backup | Check the log backup job |
| ACTIVE_TRANSACTION | There is a long-running open transaction | Find it with DBCC OPENTRAN |
| AVAILABILITY_REPLICA | The AG secondary replica is behind | Investigate the replica state and the network |
| REPLICATION | The replication reader hasn’t consumed the log | Check the Log Reader Agent |
| ACTIVE_BACKUP_OR_RESTORE | A backup or restore in progress | Wait for the job to finish |
To see log usage, DBCC SQLPERF(LOGSPACE); can be used, and to see the number of virtual log files (VLFs) on SQL Server 2016 SP2 and later, SELECT COUNT(*) FROM sys.dm_db_log_info(DB_ID('Sales'));. Small autogrowth steps create thousands of VLFs and lengthen recovery time during restore and startup. Plan the log file’s initial size and growth step with fixed MB values; remove percentage-based growth.
Using image backups and native backups together
In many system infrastructures, the SQL Server VM is backed up as an application-aware (VSS-based) image with a tool such as Veeam, and the DBA team also takes native SQL backups. The two methods work together, but who manages what must be in writing.
- Differential backup base: An image backup taken through the VSS writer is, unless marked copy-only, a normal full backup from SQL Server’s point of view and changes the base of differential backups. If native differential backups are used, enable the copy-only option in the image tool.
- Log ownership: Only one tool should take log backups. If native log backups are also taken while the image tool is configured with log truncation or its own log backup option, the chain is split across two locations or broken. Either the image tool manages the logs and no native log backups are taken, or the image tool is set not to touch the log.
- Checking the history: You can see which tool took a backup and when from the
msdbhistory.
SELECT TOP (50) bs.database_name, bs.type, bs.is_copy_only,
bs.backup_start_date, bs.first_lsn, bs.last_lsn,
bmf.device_type, bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS bmf
ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = N'Sales'
ORDER BY bs.backup_start_date DESC;
In the type column, D indicates a full, I a differential and L a log backup. Records with device_type 7 are backups taken through a virtual device, i.e. usually by VSS or third-party software. Use this query to see whether the differential chain was broken by an unexpected full backup.
Verification and testing
RESTORE VERIFYONLY checks that the backup file is readable and complete; when used with CHECKSUM, it also verifies page checksums:
RESTORE VERIFYONLY
FROM DISK = N'\\backup01\sql\Sales\Sales_FULL_20261108.bak'
WITH CHECKSUM;
VERIFYONLY doesn’t prove the logical consistency of the database. The file can be read, that’s all. Real verification means restoring the backup on a separate server and then running DBCC CHECKDB. Checklist:
- Do the recovery model and RPO match for every production database?
- Does the log backup job run successfully every 15 minutes and raise an alert on failure?
- Are backups copied to a repository separate from the SQL server and to a second location?
- Have log ownership and the copy-only setting been settled with the image tool?
- Is the full + differential + log chain restored on a test server at least once a month, with
DBCC CHECKDBrun? - Is the restore time measured and compared with the RTO?
For the technology behind targets, see the choosing technology by RPO/RTO target article, and for the number of copies and testing discipline, the 3-2-1-1-0 backup rule article. How we handle the backup chain and operations in SQL Server environments is on our backup and disaster recovery and server and cloud management pages. For a detailed reference, Microsoft’s SQL Server backup and restore document is the primary source.
Frequently asked questions
Which recovery model should be chosen for SQL Server?
If the database’s RPO is shorter than the full or differential backup interval, you need the FULL model and regular log backups. For test and reporting databases where a day of data loss is acceptable, the SIMPLE model is enough.
Why does the transaction log file keep growing?
In the FULL model, the most common reason is that no log backups are taken. Long open transactions, a lagging AG replica or replication also prevent the log from being reused. The log_reuse_wait_desc column in sys.databases shows the real reason.
If there is a Veeam image backup, do we also need SQL backups?
You may; point-in-time restore and DBA needs decide. If they are used together, the image backup must be copy-only and only one tool should take log backups. Otherwise the differential base changes or the log chain is split.
Does RESTORE VERIFYONLY prove that the backup is sound?
No. It verifies that the backup file is readable and complete, and with CHECKSUM it also verifies page checksums. For logical consistency, restore the backup to a test server and run DBCC CHECKDB.