Unanswered: Backup LOG fails when trying to backup database in SIMPLE recovery mode
I'm running a backup like this:
BACKUP LOG @database_name TO DISK = @log_location_string WITH INIT , NAME = @database_name
The log backup fails when my script hits a database that is set to simple. Problem is, it won't skip that database and continue on, it just stops right there, leaving the rest of the database logs not backed up!
How can I make my job skip backing up logs if the database is set to simple?
Correction! It looks like the Log backup job IS skipping the SIMPLE database and backing up the rest. However, in my Transact-SQL Output file, the last line in the log file stops with:
Msg 3013, Sev 16: BACKUP LOG is terminating abnormally. [SQLSTATE 42000]
Msg 4208, Sev 16: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000]
Msg 4035, Sev 16: Processed 1 pages for database 'DBNAME', file 'DBNAME_log' on file 1. [SQLSTATE 01000]
Why the heck doesn't the log file show the rest of the results?
I have options set:
Append output to step history
I'm not really sure I understand what you're talking about, but it sounds like you are using the sql maintenance plan. If you are using maintenance plan, maybe you should consider breaking the database that's using simple recovery off to a separate plan. You could write the log to the same file, and have the first plan overwrite the log, and the second one can append.
My point is that I have scheduled a job that outputs it's results to a text file. The job backs up database logs, and writes each successful database to the txt file. However, when it fails on one database (because it's set to simple) it skips it, and continues on, but does not keep writing to the txt file.