Log backup is not working in SQL Server 2017

Question

question

Dnirmania asked Jun 30, '20

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 ?

backup sql server 2017 backup failed

1

10 |1200

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

JohnM commented · Jul 03, 2020 at 01:01 AM

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 ·

Answer 1 · 2020-07-06T12:33:18Z

Dnirmania answered Jul 6, '20

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

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

question