Can you backup the log for a DB with SIMPLE recovery model? 1

[PTW]

I had some backup jobs set up, but then the database was changed from a Full recovery model to a Simply recovery model. My understanding is that in a Simple recovery model, the entries in the log is cleared when a transaction is completed.

However, I am now getting backup errors for the transaction log. Can the log not be backed-up in a Simple recovery model? Does it even make any sense to do so?

Thanks.

 

[Catadmin]

Simple recovery mode doesn't really keep a Transaction Log that you can access, backup or really do anything with. You'll need BulkLogged or Full Recovery mode enabled if you want to use your Transaction Log backup options.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[PTW]

Thank-you very much for the prompt reply. I suspected as much, but I wanted to make sure.

 

[Catadmin]

You're welcome.

Find out the reason why the db has been switched to Simple mode. It may have been a temporary thing that you can switch back. If there's a concern about large insert operations, you can always set it to Bulk-Logged which will limit the operations the Transaction Log actually logs, yet give you a place where you can recover to past the actual full backup.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???