How to fix BACKUP LOG cannot be performed because there is no current database backup

12 July,2021 by Tom Collins

Question: I'm attempting to create a backup of a SQL Server read only database , but getting an error message - BACKUP LOG cannot be performed because there is no current database backup

Why is this happening and are there any workarounds?

Answer:  Before we discuss a workaround , it's good to understand why  the message appears.

Compare these two scenarios - in both cases a new database is created and we take a backup. The difference is that in Scenario 1 the first full backup is taken after the database is placed into read only status .    Whereas Scenario 2 the  first full backup is taken before the database is placed into read only status. 

The error\information message -BACKUP LOG cannot be performed because there is no current database backup  - appears because  a modification cannot be made to the header file. Fully expected behaviour.

For scenario 2 - BACKUP LOG cannot modify the database because the database is read-only. The backup will continue, although subsequent backups will duplicate the work of this backup.  Once again - this is expected behaviour , where there's an attempt to backup the log files , but once again a change cannot be made to the header file 

 

As established - in both scenarios the messages are not unexpected. Use this information to develop a backup strategy to support SQL Server Read Only databases

Scenario 1 : Take the FIRST full backup after db is placed in read only will generate the error\warning message

create database test1;

alter database test1 set read_only with no_wait

backup database test1 to disk = 'E:\test1.bak';

BACKUP LOG [test1] TO  DISK = N'E:\test1.bak'

--MESSAGE: BACKUP LOG cannot modify the database because the database is read-only. The backup will continue, although subsequent backups will duplicate the work of this backup.

 

Scenario 2 : Take the FIRST full backup before db is placed in read only will generate the error\warning message

create database test6;

backup database test6 to disk = 'E:\test6.bak';

alter database test6 set read_only with no_wait

BACKUP LOG [test6] TO  DISK = N'E:\test6.bak'

---MESSAGE: BACKUP LOG cannot modify the database because the database is read-only. The backup will continue, although subsequent backups will duplicate the work of this backup.


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 How to fix BACKUP LOG cannot be performed because there is no current database backup


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