find if log backup can be run

Question

find if log backup can be run

Mad-Dog

SSCertifiable

Points: 6117
More actions
October 12, 2014 at 1:58 pm

#397494

Hi,
if i set database from full recovery into simple and then back to full recovery i can't run the TLog backup until i run Full or Diff DB backup.
what table or view i can query to find if i can run the TLog backup without any problems?
i need to automate a TLog DB backup and sometimes i got DB change from full to simple and then back to full.
THX

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

Mad-Dog (10/12/2014)
Hi,
if i set database from full recovery into simple and then back to full recovery i can't run the TLog backup until i run Full or Diff DB backup.
what table or view i can query to find if i can run the TLog backup without any problems?
i need to automate a TLog DB backup and sometimes i got DB change from full to simple and then back to full.
THX

Before we get to that, what is the reason for changing the DB from FULL to SIMPLE recovery to begin with? I ask because avoiding such an excursion would be the best way to do this and, if we knew the reason, might be able to help you avoid it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I agree with Jeff on this. Switching from the full recovery model to the simple recovery model and back breaks the log chain. It would be helpful to know why you are doing this.

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 3

while it's a bad idea to switch recovery modes... the OP wants a safeguard in case that happen... besides you can't run a Tlog either on a newly created database, so we still would want a safeguard to ensure backups don't run.

with a little bit of testing (and reading Ola Hallengrens scripts), I think this will do the trick:

select DB_name(database_id)

FROM sys.database_recovery_status

where database_id > 4

and last_log_backup_lsn is null