How to fix the two most common causes of SQL Server BACKUP LOG Failures

17 January,2020 by Jack Vamvas

There are some repeat questions I get from Backup or Operations staff - when they receive alerts for a  BACKUP LOG failure.

There are multiple reasons it could be happening ranging from media failure , human error, software failure ,and SQL Server database status issues e.g OFFLINE Let's leave those to one side for a moment and assume they are all ok . 

The two main reasons are; 1) the initial database FULL BACKUP not taken and  2) the database in SIMPLE RECOVERY mode 

 

1) Initial FULL BACKUP not executed when database is in FULL RECOVERY mode

Msg 4214, Level 16, State 1, Line 5 BACKUP LOG cannot be performed because there is no current database backup.

This error indicates that the initial FULL BACKUP has not  happened to initiate the LOG CHAIN. 

You can recreate the error with the code example below

--create db with FULL RECOVERY
CREATE DATABASE TEST;

--Execute a backup log statement
BACKUP LOG TEST TO DISK = 'E:\Test.TRN'


--Fix is to take a full backup and try backup log again
BACKUP DATABASE TEST TO DISK = 'E:\Test.bak'
BACKUP LOG TEST TO DISK = 'E:\Test.TRN'
--BACKUP LOG successfully processed 3 pages in 0.003 seconds (6.022 MB/sec).

As well as BACKUP LOG failures - there are other potential issues with not completing the initial FULL BACKUP - read more on The mysterious case of Pseudo SIMPLE RECOVERY model and the transaction log explosive growth

 

2) Database is in SIMPLE RECOVERY mode

The second main reason is the SQL Server database is in SIMPLE RECOVERY mode and an attempt is made to complete a BACKUP LOG. You cannot perform a BACKUP LOG on database in SIMPLE RECOVERY mode . The error message is :

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

To  recreate the error use this code example , which shows altering the database mode and attempting a BACKUP LOG

--Change backup to simple recovery  and perform a BACKUP LOG
ALTER DATABASE TEST SET RECOVERY SIMPLE
BACKUP LOG TEST TO DISK = 'E:\Test1.TRN'

--If there is a requirement for transaction log backups place the database in FULL RECOVERY mode

ALTER DATABASE TEST SET RECOVERY FULL
BACKUP DATABASE TEST TO DISK = 'E:\Test1.bak'

 

Read more on BACKUP LOG 

BACKUP LOG with TRUNCATE_ONLY is discontinued (SQL Server ...

Backing up the tail log 

Database has more than 1000 virtual log files which is excessive – informational message 9017


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to fix the two most common causes of SQL Server BACKUP LOG Failures


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer