Transaction log BU fails in Maintenance Plan fails when there is no Full BU

  • I have 3 maintenance plans for full backups, differential and transaction log.

    The job for log backups fails in when there is no full backup. Which is logical of course.

    But is there a way to prevent this error from happening? I would like an option that it will not try to backup the log when there is no full. But I can't find it.

    Is there a solution for this?

  • Unless you create your own script to test for the existence of a full backup prior to the log backups, Maintenance Plans have n0thing that does this.

    The question is why have no full backups occurred?  Are these new databases, or databases where the recover model has changed?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Ellen Heijmans wrote:

    I would like an option that it will not try to backup the log when there is no full. But I can't find it.

    I think Ola Hallengren's Maitenance Solution will do this for you. You can also check for databases in Pseudo Full Recovery and force a full or differential backup:

    SET NOCOUNT, XACT_ABORT ON;

    DECLARE @Databases nvarchar(4000);

    -- Databases in Pseudo Full Recovery
    SELECT D.[name] AS DBName
    INTO #t
    FROM sys.databases D
    JOIN sys.database_recovery_status R
    ON D.database_id = R.database_id
    WHERE D.recovery_model = 1
    AND R.last_log_backup_lsn IS NULL;

    IF EXISTS (SELECT 1 FROM #t)
    BEGIN;
    SELECT @Databases =
    STUFF
    (
    (SELECT ',' + DBName FROM #t FOR XML PATH(''))
    , 1, 1, ''
    );

    PRINT 'Differential/Full Backups being done for ' + @Databases

    EXECUTE [dbo].[DatabaseBackup]
    @Databases = @Databases
    ,@Directory = NULL
    ,@BackupType = 'DIFF'
    ,@ChangeBackupType = 'Y'
    ,@ModificationLevel = 50
    ,@CleanupTime = NULL
    ,@CheckSum = 'Y'
    ,@LogToTable = 'Y'
    ,@Encrypt = 'Y'
    ,@EncryptionAlgorithm = 'AES_256'
    ,@ServerCertificate = 'asdfgasfasdfa';

    END;
    ELSE
    PRINT 'No Differential/Full Backups Required.';

    • This reply was modified 10 months, 2 weeks ago by  Ken McKelvey.
  • Ken McKelvey wrote:

    You can also check for databases in Pseudo Full Recovery and force a full or differential backup:

    ... sys.database_recovery_status ...

    Heh... lordy. I can't believe how long I've been at this game and I've never looked at that table.  Thanks, Ken.

    As a bit of a sidebar, the failures that would occur when someone added a database without first alerting me were quite useful.  I have error handling built in where it sends me an email and then continues with the rest of the backups without failing the entire job.  That being said, I probably won't change it even knowing what in that table now. It let's me know who to take out for the next full course pork chop dinner. 😀

    --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)

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

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