SQL Server Backup and Restore: Planning for the Real Incident

How do you get to the right data state?

SQL Server provides powerful native features for backup and restore. Ola Hallengren’s Maintenance Solution automates their use. Dependable operations combine these building blocks with a specific recovery objective, complete dependencies and repeatable tests.

This article assumes you know full, differential and log backups; Part 1 explains these basics. Times and operational objectives in the figures are schematic examples unless they are explicitly marked as lab evidence.

Typical situations

Jump straight to your case:

• Error 3136After an additional full backup, the planned differential restore no longer fits.↓
• Error 4305The log to be applied starts too late for the current restore state.↓
• A specific point in timeYou need the state immediately before an accidental change.↓
• Restore takes too longDespite short backup intervals, recovery takes too long.↓
• Ola runs, but …Ola runs reliably; what is unclear is coverage, cleanup and restore testing.↓
• Encryption, AG, object storageAdditional building blocks extend the backup path.↓

Along the way, we clear up four common misconceptions:

1. “An additional full backup breaks the log chain.” A normal full backup resets the differential base. The log backup sequence is separate from that.

   resolved in: Three relationships ↓

2. “The newest file is the right one.” A full backup taken after the target time cannot be rewound.

   resolved in: Restore to a point in time ↓

3. “Keeping seven days means being able to restore seven days.” The required full base can be older than seven days.

   resolved in: Retention ↓

4. “A 35-minute restore fits into a one-hour RTO.” Only if all other phases up to sign-off fit into the remaining time.

   resolved in: RTO ↓

A route has three different relationships

The picture from Part 1 still applies: we look for a starting point, possibly a shortcut, and further sections up to the target. In the advanced case, we distinguish three things.

1. The differential baseA differential backup belongs to a specific base. A normal full backup resets this base. An older full backup and a diff on a newer base therefore do not fit together.
2. Log coverageThe required logs must cover the needed range without gaps. An additional normal full backup does not break the log backup chain. At most, it can create another entry point for other restore paths.
3. The specific selectionFor the target time, we choose a combination that is actually available. The newest file may come too late, belong to the wrong base or be only one part of a stripe set.

To track this, SQL Server manages log sequence numbers (LSNs), base information and recovery fork references, among other things. These are technical relationships, not clock times. An inventory includes the backup headers, the available media families and, if needed, the history in msdb. In a disaster, it must be possible to select the files even when the original msdb is not available (Microsoft: Restore Sequences).

A practical inventory provides the following for each backup set: database identity, type, start and end, copy-only flag, relevant LSNs, differential base, media position, all required files and any encryption dependency. Names and file dates help with orientation. They do not replace the technical mapping.

COPY_ONLY: additional backups without a new differential base

A developer needs a current copy. The regular plan uses a full backup on Sunday and daily diffs. If an additional normal full backup is taken on Tuesday, later diffs are based on this new full backup. Anyone who still considers only the Sunday full backup during the restore may fail.

For the additional copy, a deliberately chosen COPY_ONLY full backup is often the right choice:

BACKUP DATABASE [BackupDemo]
  TO DISK = N'D:\SQLBackup\BackupDemo_extra_copy_01.bak'
  WITH COPY_ONLY, CHECKSUM, COMPRESSION;

This full backup leaves the existing differential base unchanged. It can be restored like any full backup, but it does not itself become a new differential base. A diff taken afterwards therefore cannot be applied to this copy just because the copy was taken shortly before it (Microsoft: Copy-only Backups).

Lab question Does a copy-only full backup change the base of the next diff?

Setup Create full backup F0, change data, create an additional copy-only full backup C0, change data again and back up diff D2. Later, a normal full backup F1 and a new diff D3 follow.

Msg 3136, Level 16, State 1, Server …, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Msg 3136, Level 16, State 1, Server …, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Observation On SQL Server 2022 and 2025, F0 plus D2 can be restored. C0 plus D2 is rejected with error 3136. After a later normal full backup F1, the new diff D3 needs F1 as its base; F0 plus D3 fails as well.

Conclusion Additional backups belong in the operating rules. State their purpose and decide deliberately whether they should change the differential base. That is a different question from continuing the log chain.

When a log backup is missing

Full F0, log L1, log L2, log L3: the intended restore path needs complete coverage. If L2 is missing, L3 cannot simply stand in for the log ranges that are missing with it. SQL Server can reject the restore with error 4305.

In the lab, we create exactly this gap by leaving out L2 when applying the logs. Once the existing L2 is provided and applied, the sequence can continue. In the experiment, we do not delete any unknown production files and do not simulate a complete server loss.

Msg 4305, Level 16, State 1, Server …, Line 1
The log in this backup set begins at LSN 42000000053600001, which is too recent to apply to the database. An earlier log backup that includes LSN 42000000049600001 can be restored.

In practice, this means: first take inventory of all storage locations, copies and the jobs that create them. Check whether a file that exists elsewhere closes the gap or whether another suitable base opens up a new restore path.

Another regular log backup job can also be relevant: if logs are backed up to other storage, the required sections may be there. Multiple tools must fit into one common plan. Copy-only log backups affect the regular log archive point differently and need to be assessed specifically.

@ChangeBackupType='Y' in Ola looks for a backup type that can be performed when prerequisites are missing. It does not repair a lost historical file. The backup type actually created must therefore be part of monitoring.

Restoring to a specific point in time

An accidental delete at 14:07 is a different case from a hardware failure in which you want the latest reachable state. For the state immediately before the delete, you need a suitable base before the target and the required logs up to that point in time.

A full backup that only ends after the desired point in time cannot be played backward to it. If necessary, choose an older full backup. A diff must not already have passed the desired point in time either.

A schematic sequence under a new test name looks like this:

DECLARE @TargetTime datetime = '2026-10-22T14:06:50';

RESTORE DATABASE [BackupDemo_PointTest]
  FROM DISK = N'D:\SQLBackup\BackupDemo_full_01.bak'
  WITH MOVE N'BackupDemo'
         TO N'D:\SQLData\BackupDemo_PointTest.mdf',
       MOVE N'BackupDemo_log'
         TO N'D:\SQLLog\BackupDemo_PointTest.ldf',
       NORECOVERY, CHECKSUM;

RESTORE LOG [BackupDemo_PointTest]
  FROM DISK = N'D:\SQLBackup\BackupDemo_log_01.trn'
  WITH NORECOVERY, CHECKSUM, STOPAT = @TargetTime;

RESTORE LOG [BackupDemo_PointTest]
  FROM DISK = N'D:\SQLBackup\BackupDemo_log_02.trn'
  WITH NORECOVERY, CHECKSUM, STOPAT = @TargetTime;

-- Only after the required logs up to the target have been applied:
RESTORE DATABASE [BackupDemo_PointTest] WITH RECOVERY;

Files, logical file names and the point in time are examples. The required list of logs must be determined for the actual backup sets. Microsoft recommends specifying the same target time in every log restore. Afterwards, check that the intended data state was reached. Time zone, server time, application timestamps and a possible daylight saving time change are part of the mapping (Microsoft: Point-in-time Restore).

In the lab Before a targeted delete, the SQL Server time is recorded. The STOPAT restore contains the record that existed before and not the change inserted afterwards. The restore to the latest log state, by contrast, contains the later change. That is why we do not just count rows; we check the specific markers.

With BULK_LOGGED, a log backup that contains minimally logged changes cannot provide an arbitrary STOPAT within that backup. The restriction applies to that section. Include planned bulk operations in your recovery decision.

Tail-log: capturing changes that are not yet backed up

After the last regular log backup, further changes may exist. If the necessary prerequisites are met, a tail-log backup captures the remaining part of the log that has not yet been backed up. When you restore to the latest possible state, this can prevent data loss.

For a database that is still online, the following example shows a deliberate recovery intervention.

-- CAUTION: deliberate recovery intervention, not a job.
-- NORECOVERY puts the source into RESTORING; normal use is interrupted afterwards.
-- Run only as part of a coordinated recovery procedure.
BACKUP LOG [BackupDemo]
  TO DISK = N'D:\SQLBackup\BackupDemo_tail_01.trn'
  WITH NORECOVERY, CHECKSUM;

In the lab, a change that exists only in the tail is backed up and restored on a separate target together with the earlier backups. In a real incident, whether this is possible depends on which log files and database components are still usable. A lost or unusable log can block this path as well (Microsoft: Tail-log Backups).

For a target time that is already covered by existing backups, a tail-log backup is not required to reach that older state. Whether you additionally need to preserve the current remainder before further interventions remains a question of the specific case.

Ola in operation: keep selection, execution and result apart

A robust setup describes four levels:

For a traceable start, explicit options make sense: @Checksum='Y', @Verify='Y', @LogToTable='Y', compression suited to edition and load, and a deliberately defined type change. @Execute='N' helps you check the generated commands. It does not produce any evidence of a backup or restore.

A controlled ad hoc full backup can be prepared like this, for example:

DECLARE @ReturnCode int;
EXEC @ReturnCode = [DBA].dbo.DatabaseBackup
  @Databases = N'BackupDemo',
  @Directory = N'D:\SQLBackup',
  @BackupType = 'FULL',
  @CopyOnly = 'Y',
  @Compress = 'Y',
  @Checksum = 'Y',
  @Verify = 'Y',
  @ChangeBackupType = 'N',
  @CleanupTime = NULL,
  @LogToTable = 'Y',
  @Execute = 'N';
IF @ReturnCode <> 0
  THROW 50002, N'Ad hoc backup failed.', 1;

Before running it, check selection and target, and then use @Execute='Y'. Here, too, @Verify='Y' does not replace a full restore.

Important for monitoring: databases can be skipped because of their recovery model, state, a missing base or AG preference. In the lab, the LOG call for a SIMPLE database completes successfully without creating a log backup. An alert only on failed jobs would not detect this coverage gap.

Msg 4208, Level 16, State 1, Server …, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Therefore, compare the expected database inventory with the backup sets actually created, the required types, their age, and their file and copy status. Run restore tests as separate evidence. A CommandLog with six successful commands does not yet answer whether the seventh expected database was backed up (Ola: parameters and behavior, reviewed original code).

Retention: think backward from the oldest committed restore point

“Keep seven days” is ambiguous as a file rule. Should all files disappear after seven days? Or must every agreed point in time in the past seven days be reachable? For the latter, the required full base can be older than seven days.

Schematic example: the restore window begins on a Wednesday at noon. The full backup it requires is from the previous Sunday. If you delete this base purely by age, the oldest committed Wednesday can become unreachable despite newer logs. A later full backup does not help for an earlier target.

For each committed target range, therefore, plan the complete set of base, any diff, required logs, all stripes and keys. Take successful external copies and their own lifecycle rules into account. Capacity planning needs headroom for a new generation before an old one is removed.

Ola’s @CleanupTime works in hours, not days. @CleanupMode='AFTER_BACKUP' is the default; the solution has protective logic for dependent backups. That is helpful, but it is not an acceptance test of your individual historical restore window. Switching to BEFORE_BACKUP just because space is tight can reduce exactly the protection that still exists (Ola: CleanupTime).

Deleting backup files, cleaning up msdb history and maintaining the CommandLog are separate tasks. sp_delete_backuphistory removes history, not backup files. For URL targets, Ola does not support a corresponding file cleanup; the storage lifecycle rules must be matched separately to the restore dependencies.

The most effective cross-check is a test of the oldest committed recovery point. If you only ever restore the most recent full backup, you never test the difficult edge of the retention window.

Improving RTO: measure the entire process

The RTO (recovery time objective) is the agreed maximum time until the application can be used again. The right optimization starts with a breakdown of the time: provisioning, access to files and keys, transfer, full, differential and log restore, recovery, consistency check, instance objects and application sign-off.

Compression can reduce transfer and I/O effort, but it costs CPU. Stripes can make better use of suitable I/O resources; on the same bottleneck, they do not create additional physical throughput. BUFFERCOUNT, MAXTRANSFERSIZE or additional parallelism are targeted variables to measure, not universal recipes for speed.

A different mix of full, differential and log backups can change the set of files to be restored. A smaller backup can still take longer to create, and a fast backup job can leave behind a complex restore path. Therefore, compare the whole procedure under representative load and size. The small synthetic lab data in this series does not support a general RTO or throughput recommendation.

An application that is ready for use often requires more than user databases: logins and SIDs, Agent jobs, credentials, certificates, interfaces and coordinated states across several databases. The same STOPAT time and an error-free CHECKDB alone do not prove that the application as a whole is consistent from a business perspective. For related databases, marked transactions planned in advance can be part of a suitable procedure (Microsoft: Related Databases Consistently Recover).

Best practices for advanced operations

1. Name every failure scenario. An accidental change, database loss, server loss and loss of the backup target need different evidence.
2. Take inventory of restore paths. Bring together base, log coverage, recovery forks, media positions, stripes and keys.
3. Coordinate additional backups. Deliberately fit normal full backups, copy-only full backups and additional log jobs into the existing plan.
4. Test the oldest target. Do not judge retention only by the newest successful backup.
5. Check keys independently. Restore a fresh instance from secured material; actually test access and decryption.
6. Make sure errors reach the person responsible. Check procedure, scheduler, job and notification together; detect skipped databases through the coverage check.
7. Keep type changes and exceptions visible. @ChangeBackupType, minimum sizes and intervals, and replica preferences can change the expected set.
8. Measure RTO completely. A restore command is one segment of recovery. Include business sign-off and dependencies in the measurement.
9. Adopt updates in a controlled way. Record engine and Ola versions, compare options and repeat the core tests.
10. Involve a second team member. A documented procedure that has been successfully handed over is more dependable than knowledge held by one person alone.

To-do lists

One list for ongoing operations, one for a specific problem. Your check marks are stored only in your browser.

Frequently asked questions

Check your understanding

Two questions from everyday practice. Think for a moment, then turn the card over.

• Question 1 · Differential base

  You have a full backup from Sunday, an additional copy-only full backup from Tuesday and a diff from Wednesday. Which full backup does the diff belong to, provided no other normal full backup was taken in between?

  SunTueWed

  Answer

  To the full backup from Sunday.

  It is the matching normal full base. The copy-only full backup did not change it and is not a differential base itself. The actual identity is checked with the metadata and the restore.

  Reproduced in the lab: Figure 2 ↑

• Question 2 · RTO

  All files are available, and the restore takes 35 minutes. Does that meet an RTO of one hour?

  35 minof60 min

  Answer

  Not decided yet.

  Only if the other agreed phases, including provisioning, access, checks and application sign-off, are completed within the remaining time. The SQL duration alone is not enough to tell.

  To the phase calculator: Figure 12 ↑

Origins and thanks

Ola Hallengren’s SQL Server Maintenance Solution automates backups with SQL Server’s native features and logs what it does. Source code and documentation are freely available under the MIT license (repository). Thank you to Ola Hallengren and to the community that uses the solution, tests it and carries it forward.

Where this article describes limits, they are limits of scope, not shortcomings: DatabaseBackup backs up and verifies; you build the restore plan, the expected inventory and the tests around it. It is exactly this division of tasks that makes the solution easy to understand and verify.

This article is our own text, with our own lab tests. Ola Hallengren has neither reviewed nor approved it; the conclusions and any errors are ours. For parameters and behavior, Ola’s own documentation is authoritative. We reviewed the state at commit 41617578 (source timestamp Sep 12, 2026); newer versions may behave differently.

Method and limits

Facts as of Oct 22, 2026. Ola’s original code frozen at commit 41617578643e722b790ea4d57ef80f8de38ae747. Our own lab tests selected native restore paths and Ola calls on SQL Server 2022 and 2025 on Linux.

Not tested: no production Windows integration, no server or site loss, no availability group, no object storage, no real retention window, no key loss scenario. Also based only on the documentation: BULK_LOGGED, system databases, scheduler and complete application recovery. That a normal full backup does not break the log chain is documented by Microsoft; we did not run a separate test for it. The affected figures are therefore labeled “Diagram” or “Documented concept”.

Further reading