When we add a new database (restore from a staging instance) to our live instance we have a job which sets it to Full recovery (in case the tech forgot that) and then a maintenance plan that runs a Full backup each night. We also have a maintenance plan that runs a transaction log backup every 30 minutes. How do I stop the latter failing whilst we are waiting for the first Full backup to complete? I’m thinking I maybe cannot achieve this with maintenance plans but need a bespoke agent job?

  • Successful full backup makes an entry into msdb.dbo.backupset. Now write a small TSQL to check if there is row for the database in the backupset table if it has do not do log backup. Yes it would not be possible from MP.
    – Shanky
    Sep 7, 2017 at 13:36
  • 1
    Ola Hallengren's backup solution has a built-in failover in case of that. The parameter being ChangeBackupType.
    – John K. N.
    Sep 7, 2017 at 13:43
  • @Shanky - yeah, I can see how to do it the way you say I just wanted to double check before stepping outside of the MP regime.
    – Paul H
    Sep 7, 2017 at 13:43
  • @hot2use - thanks I've looked at their solutions to other maintenance tasks before, I should have realised that they would have nailed it.
    – Paul H
    Sep 7, 2017 at 13:46

1 Answer 1


Check out Ola Hallengren's maintenance scripts. They have a @ChangeBackupType parameter that lets you automatically escalate to fulls if necessary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.