1

I've created a new database using the FULL recovery model. Now I am trying to take a log backup without first taking a full backup.

I'm aware that SQL Server won't allow us to do so, but I want to know why SQL Server is preventing it?

Below is the script I've used for testing purposes:

CREATE DATABASE TESTING
GO
BACKUP LOG TESTING TO DISK='G:\TESTING_LOG.TRN'
6

When a database is switched to FULL recovery, it behaves as if it was in SIMPLE recovery until you take a full database backup. Up to that moment, you cannot take log backups, because the log gets truncated on CHECKPOINT, as happens in SIMPLE recovery.

See this article by Paul Randal for reference.

Log backups form part of a chain that allow the whole database to be recovered. The chain starts with a full backup (complete copy), and may continue from that point with further differential or log backups.

Recovery example

Image taken from Complete Database Restores (Full Recovery Model)

A transaction log backup contains the information needed to:

  • Redo all committed changes to the database since the last full or log backup
  • Undo in-progress (not yet committed) changes at the time the log backup was taken

In principle, it would be possible to recreate a database from log information alone, if every log backup ever was available, including the initial creation of the database itself, but that is not the way it was implemented.

As it is, a full backup is necessary is start the recovery path.

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.