Breaking the chain

Question: I’m trying to perform a point-in-time restore but two of the log backups were not taken by the log backup job. Will I be able to use those log backups and will the log backup chain be broken now?

Answer: The good news is that you should be fine.

Whenever you want to restore to a point in time, say X, you must be able to perform a restore sequence starting with your most recent full database backup and ending with a log backup that covers point in time X (I mentioned a full database backup, but this also applies to restoring a single filegroup, a single file, or performing a partial restore – see my recent SQL Server Magazine article here).

A typical restore sequence will always start with a full backup, and then a series of log backups, finishing with the one that covers point X. An alternative may be to restore a differential backup as a way of being able to skip a large number of log backups, thus making the restore sequence smaller and faster.

If at any point there is a missing or damaged backup, with no alternative backup to restore, the restore sequence cannot progress any further.

The log backup chain (also known as the log chain) is the term used to describe an unbroken sequence of transaction log backups, from whenever the chain started (usually with a full database backup), to the most recent log backup performed. A log backup contains all the transaction log generated since the previous log backup, and so it is imperative that the chain is not broken otherwise the restore sequence cannot progress past the break in the chain, unless a differential backup exists to allow the gap to be bridged.

If a backup from the chain is missing, or damaged, then the chain is broken. This is a good reason to keep multiple copies of backups, and also to test them regularly to ensure the backup process is producing valid backups. You can also break the log backup chain by switching to the Simple recovery model, where log backups are not possible.

In the case of the original question, it does not matter how the log backups were taken – as long as they are in sequence then the log backup chain is not broken and a point-in-time restore should be possible.

SQL Server Books Online has more information about log chains here.