question

Dnirmania avatar image
Dnirmania asked

Log backup is not working in SQL Server 2017

Hello Everyone

I have a database which was in "Read_only" mode and "Simple" recovery model. After changing DB mode to Read_write and recovery model to Full. I ran full backup which was completed successfully but when I try to take log backup, my process failed with following error.


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

SO.I would like to ask you , is there a possibility that log backup failed because database was in Read_only from so long and no DML queries were executed on DB ?

backupsql server 2017backup failed
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No. As soon as you changed it to FULL recovery model and took a full backup it would have set the log chain. Just curious, were you backing up the log of the correct database?

If you do a select recovery_model,* from sys.databases where name = '[Databasenamehere]' what does recovery_model say?

0 Likes 0 ·

1 Answer

· Write an Answer
Dnirmania avatar image
Dnirmania answered

Thanks @JohnM for your response but i think, I Figured out the cause of this issue. In my PS script, I was first changing the Recovery model of DB and then Changing it to read_write.Sounds weird right? Somehow, PS cmdlet changing the recovery of DB without updating its mode and metadata. I used DBAtools cmdlet to do that. Now I have updated my PS script and Its working as expected.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.