Statement Backup Log is Not Allowed for a SQL Transaction Log Backup

By: K. Brian Kelley | Comments | Related: > Backup

Problem

I have a SQL Server database for which I need to do a point-in-time restore. However, I'm unable to take a transaction log backup, as SQL Server gives me an error when I try to create a log backup. I checked and unfortunately the database is in Simple recovery mode. I then switched the database to Full recovery mode and received a different error when trying to take a transaction log backup. How can I get a transaction log backup to accomplish a point-in-time restore?

Solution

Unfortunately, if the SQL Server database is in Simple recovery mode, you won't be able to take a transaction log backup to try and capture the past transactions. You can only create transaction log backups if the database is Full or Bulk-Logged recovery. Let's take a look at some examples and the errors.

Demo to Create a SQL Server Transaction Log Backup

Let's set up a quick demo database:

CREATE DATABASE [TestLogBackup];
GO 

ALTER DATABASE [TestLogBackup] SET RECOVERY SIMPLE;
GO

Let's verify our database is in Simple recovery mode.

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'TestLogBackup';
GO

We should see that the recovery mode is listed as Simple:

Now let's take a full backup, so we have one for the database.

-- This assumes you have a directory C:\Temp. 
-- Modify to fit your file path
BACKUP DATABASE [TestLogBackup]
TO DISK = N'C:\Temp\TestLogBackup_Full.bak'
WITH INIT;
GO

We'll see why taking a full backup while the database is in Simple mode doesn't help us if we switch to the Full recovery mode a bit later in the article.

SQL Server Transaction Log Backup Error

With our demo database set up, let's try a transaction log backup.

-- Attempting to backup TestLogBackup
-- Transaction log
BACKUP LOG [TestLogBackup]
TO DISK = N'C:\temp\TestLogBackup.trn';

And we'll get an error "The statement BACKUP LOG is not allowed with the recovery model is SIMPLE":

The reason we're getting the error is because Simple recovery mode doesn't support transaction log backups. This is by design. To understand why, you'll need to understand how the transaction log is structured as well as the checkpoint process.

SQL Server Virtual Log Files, Dirty Pages and the Checkpoint Process

Let's try and keep this at a high level since entire books have been written on the transaction log.

SQL Server partitions the transaction log file into one or more virtual log files (VLFs). These virtual log files are what contain the transaction information which allows SQL Server to roll forward or roll back each transaction. In the Full and Bulk-Logged recovery modes, a VLF stays active until it is backed up by a transaction log backup. When a VLF is active, SQL Server can't reclaim the space used by the VLF to be reused by newer transactions. With these two modes, there is an expectation that you will need to preserve transactions, so the VLF isn't allowed to be overwritten until you take that transaction log backup.

In contrast, with Simple recovery mode SQL Server attempts to keep the active portion of the transaction log as small as possible given considerations for performance. SQL Server will mark a VLF inactive as soon as it can be sure that the transaction has been successfully written to the data file(s). Inactive VLFs don't have to be retained, meaning SQL Server can recycle the space. This is why you don't have to worry about transaction log backups to keep the space down when a database is in simple recovery mode.

Shouldn't the data be written to the data file when the transaction completes? Not necessarily. Keep in mind that an RDBMS needs to perform well. As a result, SQL Server will attempt to minimize disk access for each transaction. When talking about a data change, SQL Server first writes the pertinent information to replay or rollback the transaction in the transaction log file. However, for the purposes of performance, SQL Server will make the appropriate changes corresponding to the data file in memory next, not to disk, because writing to memory is faster. After all, if there is a crash or catastrophic issue, SQL Server already has what it needs to recover because it has the transaction log entries. When pages of data exist where they have been changed in memory but not on disk, we say those pages are dirty pages.

When do the dirty pages get written to disk? SQL Server has a checkpoint process that will periodically take those dirty pages and then commit them to disk. While this can also be done manually via the CHECKPOINT command, this isn't a typical practice and you shouldn't do it unless there's a compelling reason to do so. When a database is in Simple recovery mode, SQL Server will also mark a VLF inactive when a checkpoint clears any remaining dirty page(s) for transactions recorded in that VLF. At this point, the space for the VLF can be re-used by SQL Server as needed.

As a result, if the database is in Simple recovery mode, SQL Server can't guarantee it has preserved all the VLFs in the transaction log since the last full or differential database backup. An initial transaction log backup, which is supposed to contain all such changes, can't be taken without that guarantee.

Why Doesn't a SQL Server Transaction Log Backup Work When Database is Changed to Full Recovery Mode?

Let's go back to our demo database and try and switch to Full recovery mode and then take the transaction log backup:

-- Try to switch it to full recovery model
ALTER DATABASE [TestLogBackup]
SET RECOVERY FULL;
GO

-- Attempting to backup TestLogBackup
-- Transaction log
BACKUP LOG [TestLogBackup]
TO DISK = N'C:\temp\TestLogBackup.trn';

This produces the second error, "BACKUP LOG cannot be performed because there is no current database backup".

Why are getting an error saying there's no current database backup when we took a full backup right after we created the database? The answer has to do with log chains.

Understanding SQL Server Log Chains

One or more sequential transaction log backups is referred to as a log chain. However, that log chain has to be anchored to something. That something is either a full or differential backup and it has to be one of these backup types after the recovery mode was switched to Full. Just as in Simple recovery mode, SQL Server can't guarantee that a transaction log backup will have captured all of the transactions since that full or differential backup that was taken in Simple mode.

Therefore, without that new full or differential backup, there's no place for a transaction log backup to tie in with for recovery. SQL Server does record keeping to track what the latest full backup was before the transaction log backup. If there were one or more differential backups after the full backup, but before the transaction log backup, it tracks the latest differential backup, too. This information is stored in the transaction log backup. If SQL Server doesn't have that information, it can't take the transaction log backup. I'm greatly simplifying things here but at a conceptual level that's what's going on. Therefore, if you don't have that full or differential backup after switching to Full recovery mode, you won't be able to take a transaction log backup.

So, to get around the second error above, you need to create a new database backup now that the database is in Full recovery and then you can take a log backup as follows.

-- This assumes you have a directory C:\Temp. 
-- Modify to fit your file path
BACKUP DATABASE [TestLogBackup]
TO DISK = N'C:\Temp\TestLogBackup_Full.bak'
WITH INIT;
GO

-- Attempting to backup TestLogBackup
-- Transaction log
BACKUP LOG [TestLogBackup]
TO DISK = N'C:\temp\TestLogBackup.trn';

SQL Server Transaction Log Backups Just Don't Work When Database is in SIMPLE Recovery Model

Unfortunately, if the database is in Simple recovery mode, there's not a way to create a log backup. You can restore up to the latest combination of full and differential backups that's prior to the point in time. Either that or you have to go with what's currently in the database and somehow correct the data, if that's possible.

There's no supported way to get point-in-time recovery if the database wasn't in Full recovery mode. It's possible in some situations with Bulk Logged recovery mode, but you won't be able to in Simple.

The Simple mode wasn't designed for a point-in-time recovery.

Next Steps

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

The Statement Backup Log is Not Allowed for a SQL Server Transaction Log Backup