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 '17 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 '17 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 '17 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 '17 at 13:46

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, privacy policy and cookie policy

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