1

What is the difference between following restore options:

A. Full backup and applying to it subsequent log backups.

B. Snapshot backup and applying to it subsequent log backups.

Note: this is not database snapshot but snapshot backup (for example: a full backup of type snapshot performed by VSS writer)

1
1

Let's have a look at your options:

Full backup and applying to it subsequent log backups.

This option allows you to restore the database to any point-in-time, provided you have an unbroken chain of TLOG backups.

Advantage: With this option you have a physical copy of the database backup that can be restored to other servers. The backup is not linked to the disk/volume it was taken from.

Snapshot backup and applying to it subsequent log backups.

This option allows you to bring a whole system (when snapshotting the server) back online in a matter of seconds or to restore a snapshot backup and to apply additonal TLOG backups.

The requestor can issue a restore specifying the SetAdditionalRestores(true) option. This option indicates that the requestor is going to follow up with more rollforward restores (such as log restore, differential restore etc.). This instructs SQL Server not to perform the recovery step at the end of the restore operation.

A snapshot of a database is copy of the disk data at a given point-in-time:

Component-based backups are preferred and recommended with the SQL writer, since the application (VSS backup application) will explicitly select the databases from the metadata that is returned from the SQL writer. The snapshot set should include all the volumes necessary to back up those databases. The VSS infrastructure does not automatically add the volumes that are required for the selected set of databases. All backing volumes should be included in the volume snapshot set. It is the responsibility of the backup application to make sure that all backing volumes are included in the snapshot set. The SQL writer will detect torn databases (with backing volumes outside the snapshot set) and fail the backup.

Reference: SQL Server Backup Applications - Volume Shadow Copy Service (VSS) and SQL Writer (Microsoft | SQL Docs)

Disadvantage: The snapshot backup is related to the disk/volume it was taken from.

You might be able to create a database backup from the VEEAM snapshot, but I don't know the product that well.

3
  • In the case of snapshot backup via VSS, are you sure that transaction logs cannot be applied?
    – variable
    Sep 30 at 6:47
  • I'm using the veeam tool to take snapshot backup every Sunday and transaction log backups every 15mins. The tool allows me to choose the restore point (snapshot backup) and then choose the point in time recovery values between the previous restore point and the next restore point (it seems to be able to do this by applying the transaction logs).
    – variable
    Sep 30 at 7:01
  • You are right: The requestor can issue a restore specifying the SetAdditionalRestores(true) option. This option indicates that the requestor is going to follow up with more rollforward restores (such as log restore, differential restore etc.). This instructs SQL Server not to perform the recovery step at the end of the restore operation.
    – John K. N.
    Sep 30 at 7:08
2

A Snapshot is not really a type of database backup in SQL Server. Rather it is a separate feature to quickly restore the database to a point in time place as opposed to using a Full Backup. You can read about the differences and how Snapshots are used in this article.

But the important thing to note is you wouldn't be able to restore Transaction Log Backups after restoring directly from a Snapshot alone. Transaction Log Backups can only be restored after a base Full Backup is restored first, that is part of the same backup chain.

There is a third type of backup in SQL Server called Differential Backups which are used to improve recovery time of larger databases. They do so by backing up all changes that have occurred since the last Full or Differential Backup. Typically they are less heavy of an operation than a Full Backup but more heavy than Transaction Log Backups, so they can be ran a few times throughout the day - such as on an hourly schedule (or whatever fits the needs of the database being backed up). This results in less Transaction Log Backups files that are needed to be restored, and therefore a quicker recovery time.

But please note, even when using Differential Backups, a base Full Backup is still always needed to be restored first. This how backups work in SQL Server, the base Full Backup is always the starting point before restoring any other types of backups. So only your option A is valid, option B wouldn't work.

5
  • It is architectuallyt possible to apply log backups on a snapshot. This is what Veeam's log backup agent does, for instance (it produces log backups to files and manage those log backups files for you so Veeam later can restore the snapshot and subsequent log backups). Sep 28 at 13:35
  • @TiborKaraszi I can't speak on systems outside of SQL Server, but per Microsoft's Books Online on Transaction Log Backups a Full Backup is a prerequisite to being able to take and restore Transaction Log Backups: "Minimally, you must have created at least one full backup before you can create any log backups.". So I'd have to assume Veeam does this as well if it's managing it. Additionally we're not sure what type of Snapshot OP is referring to, so I spoke...
    – J.D.
    Sep 28 at 14:11
  • ...in regards to the SQL Server Snapshot feature, which does not support restoring directly off of Transaction Log Backups without first doing a Full Backup restore.
    – J.D.
    Sep 28 at 14:12
  • 1
    Ah, sorry J.D. I understand now that you refer to the database snapshot feature in your comment, but I though you meant snapshot backup (like the one that Veeam does). Sorry for the confusion. I.e., both out comments are valid, depending on if the reader means database snapshot or snapshot backups. :-) Sep 28 at 14:27
  • @TiborKaraszi No prob! And agreed, the information in your comment is helpful too, thanks! Depends on what kind of snapshotting OP / other readers are interested in.
    – J.D.
    Sep 28 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.