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.
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |