SQL Server Backup and Restore: The Basics

Your backups are running. Can you also restore from them?

A backup job reports success. There are files on the drive. That is a good start. Whether you can resume work after an outage only becomes clear in the next step: Can you reach the backups you need, restore them in the right order and verify the result from a business perspective?

SQL Server comes with the backup and restore features you need for this. Ola Hallengren’s Maintenance Solution helps automate their execution. In this article, we build the basic model, run a first restore and show how individual commands become a workable operational routine.

The short answer: A full backup can be the foundation of a restore. Differential backups extend the full backup they belong to. In FULL or BULK_LOGGED, transaction log backups carry the data state forward along the available log history. Whether this route leads to your target is something you verify with a real restore.

This is especially worthwhile if any of these situations sound familiar:

• “The backup has been running for years. The last time we restored was during the installation.”
• “We have .bak files, but we don’t know exactly which ones belong together.”
• “The job is green, yet backups of a new database are missing.”
• “We want to use Ola and need an approachable way to get started.”

First, clarify: How much can be lost, and how long can it take?

Before you set up a schedule, you need two business objectives.

Recovery Point Objective, or RPO: What is the maximum data loss that is acceptable for the failure scenario under consideration? If no more than 15 minutes may be lost, the process must provide a sufficiently recent restorable state. A log backup every 15 minutes is one possible technical foundation. Failed backups or files that have not yet been transferred can make the result you actually achieve worse.

Recovery Time Objective, or RTO: Within what time should the agreed service be available again? Getting back up and running often involves a replacement system, file transfer, the restore itself, checks and releasing the application. The restore time reported by the SQL command covers only part of that.

A small planning example, not a lab measurement: The business side accepts at most 15 minutes of missing changes and two hours of interruption. Then the statement “We back up at night” is not enough. You need a route to a sufficiently recent data state and a proven recovery within this time budget. The objectives must fit the specific failure scenario: a deleted record places different demands on you than the loss of an entire site.

Three backup types, one shared route

Think of the restore as a route. A full backup is one possible starting point. A matching differential backup shortens the way to a later state. Log backups take you further from there. Some sections are interchangeable; others must be available without gaps.

F0FullD1DifferentialL1LogTargetdesired data state

Full: a complete database backup

A normal full backup backs up the database and enough of the transaction log to restore a consistent database state from the backup. SQL Server can keep working in the meantime; a full backup is not a mere file copy of a database that has been shut down.

A full backup on its own gives you a restorable state from that backup. Changes made afterward require further matching backups. In addition, a normal full backup resets the base for later differential backups. It does not break an existing log backup chain. We will need this distinction in Part 2.

Log: carrying the available history forward

In FULL and BULK_LOGGED, transaction log backups can back up the history needed for recovery. For a restore, you need the backups that cover the range from the chosen data state to the target without gaps. Sorting files by modification date alone is not enough for that.

A log backup is not a backup of the entire .ldf file. Nor does it automatically shrink that file. It can create the conditions for reusing log space that is no longer needed; other reasons for retaining the log may still apply. A large log is therefore not automatically solved by a one-time log backup or by regular shrinking.

The route planner shows how this fits together. Choose a target state and see which files you need for it.

SIMPLE, FULL and BULK_LOGGED are operational decisions

The recovery model helps determine which restore paths are available at all.

Choose the recovery model based on your recovery objectives. Switching to SIMPLE just to get rid of a growing log can destroy a restore path you need. In the route planner, you can see the difference with the “Recovery SIMPLE” switch (Microsoft: Recovery Models).

The first native backup and restore test

We use a dedicated practice database BackupDemo and a new target name BackupDemo_RestoreTest. The path is a Windows example on the SQL Server machine. On Linux or with other directories, adjust it accordingly. The SQL Server service needs access to the target; a drive that is visible on your workstation is not automatically reachable for the service.

The practice database must be created beforehand and contain test data. Use a new file name for each of your own backup runs.

-- Practice database BackupDemo; Windows example path on the SQL Server machine
BACKUP DATABASE [BackupDemo]
  TO DISK = N'D:\SQLBackup\BackupDemo_full_01.bak'
  WITH CHECKSUM, COMPRESSION, STATS = 10;

CHECKSUM enables backup checksums and the intended checks of existing page checksums. COMPRESSION can reduce file size and I/O, but it uses CPU and must be supported by your version and edition. STATS shows progress. None of these options replaces the restore test.

First, get to know the file

-- Reads only the backup file; does not change any database
RESTORE HEADERONLY
  FROM DISK = N'D:\SQLBackup\BackupDemo_full_01.bak';

RESTORE FILELISTONLY
  FROM DISK = N'D:\SQLBackup\BackupDemo_full_01.bak';

RESTORE VERIFYONLY
  FROM DISK = N'D:\SQLBackup\BackupDemo_full_01.bak'
  WITH CHECKSUM;

HEADERONLY shows backup information, FILELISTONLY the database files. VERIFYONLY checks, among other things, that the backup is readable and complete and, where backup checksums exist, the corresponding integrity. It does not perform a full restore followed by a structural or application check (Microsoft: RESTORE VERIFYONLY).

Then actually restore

The logical names for MOVE must come from FILELISTONLY. In this example, they are BackupDemo and BackupDemo_log; other databases can have several additional files. Every file you need requires a suitable target path. The directories must exist, and the new database name must be available.

-- CAUTION: run only on a test target and under a NEW database name,
-- never over the live database. No WITH REPLACE.
-- Take the logical names from RESTORE FILELISTONLY.
RESTORE DATABASE [BackupDemo_RestoreTest]
  FROM DISK = N'D:\SQLBackup\BackupDemo_full_01.bak'
  WITH MOVE N'BackupDemo'
         TO N'D:\SQLData\BackupDemo_RestoreTest.mdf',
       MOVE N'BackupDemo_log'
         TO N'D:\SQLLog\BackupDemo_RestoreTest.ldf',
       RECOVERY, CHECKSUM, STATS = 10;

DBCC CHECKDB ([BackupDemo_RestoreTest]) WITH NO_INFOMSGS;

Here, RECOVERY ends the restore sequence and makes the database usable. For further differentials or log backups, the sequence must stay open beforehand with NORECOVERY. A premature RECOVERY can therefore force you to start over. Try it out:

Then check the expected test records and one business transaction. A technically consistent database can still contain the wrong point in time. For a complete application test, you may also need logins, jobs, certificates or other databases.

What does each check prove?

File present, file checked, database restored, result verified: in day-to-day work, these four kinds of evidence easily blur together. But each level answers a different question. VERIFYONLY, the restore, CHECKDB and business-level tests each provide different evidence.

Result of the basic experiment: A differential can be checked successfully with VERIFYONLY even though it cannot yet be restored on its own as a new database. In our own lab on SQL Server 2022 and 2025, the restore succeeds with the matching base full backup. The file check and the complete restore path answer different questions.

Combining full, differential and log backups sensibly

One possible scheme: regular full backups, differential backups in between, and frequent log backups in addition under FULL. This is an example pattern, not a prescribed standard for every database.

A schematic daily routine: full backup at 00:00, differential at 12:00, log backups every 15 minutes. For a target state in the afternoon, you choose the suitable starting point and the log backups needed after it. For a point in time before the midday differential, you cannot use that later differential. This is exactly the scheme behind the route planner (Figure 3) ↑: move the target once before and once after 12:00.

The right frequency depends on the volume of changes, database size, backup window, reachable target, desired data loss and tested restore time. A chain of many small log backups can allow little data loss and still cause a long recovery. The schedule must take both objectives into account.

Ola Hallengren: good automation for native backups

Ola Hallengren’s SQL Server Maintenance Solution provides, among other things, the procedure DatabaseBackup. It selects databases, generates native backup commands, organizes directories and file names and can then run RESTORE VERIFYONLY. With CommandLog, executed commands and their results become traceable.

This is valuable community work. Especially with several databases, a maintained shared procedure helps instead of running many hand-built individual jobs. But Ola does not define your RPO and RTO objectives, nor a complete restore plan. The Maintenance Solution does not include a general procedure that automatically restores your database to the desired point in time from arbitrary files (official Maintenance Solution, DatabaseBackup).

A manageable way to get started

1. Download the solution from the official source. Record the version and checksum, and review changes before an update.
2. Choose a dedicated administration database and read the installation options. The complete script can create more objects and jobs than you need for a first attempt.
3. Make sure the SQL Server service can access the backup target. Job owner and file access identity are separate questions.
4. Start with a single test database and a dedicated directory.
5. First, have the commands displayed with @Execute = 'N'. Check the selection, paths and options; this preview call does not yet prove successful file access or a restore.
6. Run the test for real, check the resulting backups and restore them with native commands.

An example after installing into the administration database DBA:

-- Preview: @Execute = 'N' only displays the commands.
-- Set @Execute = 'Y' only after reviewing the output.
DECLARE @ReturnCode int;

EXEC @ReturnCode = [DBA].dbo.DatabaseBackup
  @Databases   = N'BackupDemo',
  @Directory   = N'D:\SQLBackup',
  @BackupType  = 'FULL',
  @Compress    = 'Y',
  @Checksum    = 'Y',
  @Verify      = 'Y',
  @CleanupTime = NULL,
  @LogToTable  = 'Y',
  @Execute     = 'N';

IF @ReturnCode <> 0
  THROW 50001, N'DatabaseBackup reported an error.', 1;

After reviewing the output, set @Execute = 'Y' for the actual backup. For the corresponding differential and log runs, set @BackupType to DIFF or LOG, respectively. Do not simply count a LOG job as a success for every database: in SIMPLE, this backup type is not available.

@CleanupTime = NULL deliberately skips automatic deletion here. Configure cleanup only once your retention and restore path is settled. This is a starter setting, not a recommendation for unlimited retention.

Tested in the lab: Ola creates full, differential and log backups with checksums and subsequent verification. These files can be restored with native RESTORE commands. Also tested: the LOG call for a SIMPLE database can end with return code 0 without creating a log backup. That is why, in addition to the return code, we also check the expected backup sets for each database (original code at the tested commit).

Best practices for getting started

• Objectives before schedules. Name the acceptable data loss and recovery time from a business perspective, then choose backup types and intervals.
• Capture newly created databases. An old job that runs successfully can miss new databases. Compare the inventory with the actual coverage.
• Use unique files. Understand overwriting, appending to media and backup positions. To get started, use new file names instead of unclear collective files.
• Combine checksums with real restore tests. Verification, restore, CHECKDB and business-level tests provide different evidence.
• Get backups out of the failure domain. Another file on the same server does not protect against losing that server completely. Plan for separate copies and access rights.
• Think about keys. Encrypted backups require the matching keys and certificates. These must remain available even after the original server is lost.
• Make responsibility visible. Who receives an alert, who assesses it, and who checks the successful rerun?
• Test again after changes. After a version change, new paths, a changed service account or new backup software, check the restore path again.

Your to-do list for a first reliable baseline

Check off what is done. Your progress is saved only in this browser.

• List all databases needed in production and their recovery models.
• Record the RPO, RTO and failure scenario under consideration for each application.
• For each database, determine the most recent successful full, differential and log backups that are reachable from the restore target.
• Provide an isolated restore target with enough space and a suitable engine.
• Perform a complete test restore under a new name.
• Check CHECKDB, the expected records and at least one business transaction.
• Include the required certificates, keys and instance objects in the recovery documentation.
• Record the time for provisioning, file access, restore and release separately.
• For Ola, document the version, parameters, schedules and actual database coverage.
• Record the person responsible, the alerting path and the date of the next test.

Frequently asked questions

Check your understanding

Origins and thanks

The SQL Server Maintenance Solution is the work of Ola Hallengren and is available under the MIT license (ola.hallengren.com). Especially with several databases, it replaces many hand-built individual jobs with one maintained, shared procedure. Thank you to Ola Hallengren for this work, and to the community that uses, tests and carries the solution forward.

This article is our own account, in our own words, with our own examples and lab tests. Ola Hallengren has neither reviewed nor approved it; the conclusions and any errors are ours. We tested the state at commit 41617578. Newer versions may change parameters and behavior; the DatabaseBackup documentation is authoritative.

Method and limits

Facts as of Oct 22, 2026. Native functional tests and Ola calls on SQL Server 2022 and 2025 in an isolated Linux lab, using synthetic data only. No production acceptance, no general performance figures and no complete platform test. The Windows paths in the text are explained examples; in the lab, the same commands ran with Linux paths.

Not tested in the lab, explained based on the documentation: scheduling with Agent or Task Scheduler, system databases, encryption, BULK_LOGGED, availability groups, Windows paths and a complete application recovery. The run times of the small tests are not performance or RTO measurements and are not used here.

Sources: Microsoft: Backup and Restore · Recovery Models · Differential Backups · RESTORE VERIFYONLY · Ola Hallengren: DatabaseBackup.

Further reading

Continue with Part 2: restore paths, points in time and operations. There we examine COPY_ONLY, missing log backups, tail-log backups, retention and the limits of automated processes.