BACKUP failed to complete the command BACKUP LOG

29 June,2011 by Tom Collins

BACKUP failed to complete the command BACKUP LOG is a common SQL Server error message.  In large IT departments a member of the Operations Team logs the call to a DBA queue.

 An example message is

The 09.30 log backup also failed checked sqllog - 28-06-2011 09:30:22 Backup of DatabaseUsers failed. ACO5422E Received the following from the MS SQL server: An exception occurred while executing a Transact-SQL statement or batch. BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally

Step 1 – Go to the SQL Server Logs and find the message.

Backup log failure 
 
Step 2 – Investigate database configuration changes

If you observe a sequence of events , such as RECOVERY to SIMPLE   followed by a RECOVERY to FULL , then  it’s a candidate for the root cause of the error message

Backup log simple recovery 
Backup log full recovery 

When a SQL Server database  -  has a LOG BACKUP schedule , and the Recovery Model changes from FULL to SIMPLE , then upon changing back to FULL – and FULL BACKUP must occur to start a new log backup chain

 If  you can’t see these messages – consider investigating other causes.A similar problem is  Copy only backup in SQL server 2005 and not break the backup chain

 Step 3 – Request from Operations or a SQL Server  DBA to take a manual FULL BACKUP of the database.

This should allow the next request for a LOG BACKUP to complete successfully.


Author: Tom Collins (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 BACKUP failed to complete the command BACKUP LOG


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