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?
July 10, 2023 at 1:58 pm
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/
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.';
July 10, 2023 at 7:37 pm
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply