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.
