Hier die Scripte:

Anlage des Operators:

USE [msdb]

GO


EXEC msdb.dbo.sp_add_operator @name=N'Administration',

@enabled=1,

@weekday_pager_start_time=90000,

@weekday_pager_end_time=180000,

@saturday_pager_start_time=90000,

@saturday_pager_end_time=180000,

@sunday_pager_start_time=90000,

@sunday_pager_end_time=180000,

@pager_days=0,

@email_address=N'DEINEEMAILADRESSE',

@category_name=N'[Uncategorized]'

GO

Registrierung eines Custom-Alerts:

EXEC sp_addmessage

    @msgnum = 50008,

    @severity = 16,

    @msgtext = 'Warnung! Veraltetes Transactionlog-Backup! %s',

    @lang = 'us_english';

Anlage eines Custom-Alerts:

USE [msdb]

GO


EXEC msdb.dbo.sp_add_alert @name=N'SQL Alert - Sev 16 Error 50008: Outdated Transactionlog Backup Alert',

@message_id=50008,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

Anlage des SQL Server Agent Jobs (Eine individuelle Konfiguration des Intervalls ist nötig)

USE [msdb]

GO


BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0


IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Log Shipping' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Log Shipping'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback



END



DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Database Transactionlog Backup Monitor',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'Log Shipping',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check For Last TransactionLog Backup',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'-- Variablen für die dynamische Nachricht

DECLARE @DatabaseList NVARCHAR(MAX);

DECLARE @Message NVARCHAR(MAX);

DECLARE @ServerName NVARCHAR(128) = COALESCE(@@SERVERNAME, CONVERT(NVARCHAR(128), SERVERPROPERTY(''MachineName'')));

DECLARE @InstanceName NVARCHAR(128) = CONVERT(NVARCHAR(128), COALESCE(SERVERPROPERTY(''InstanceName''), ''Default Instance'')); -- Instanzname mit Konvertierung

-- Abfrage für das letzte Transaction Log Backup

WITH BackupData AS (

    SELECT

        database_name,

        MAX(backup_finish_date) AS LastBackupDate

    FROM msdb.dbo.backupset

    WHERE type = ''L'' -- Nur Transaction Log Backups

    GROUP BY database_name

    HAVING MAX(backup_finish_date) < DATEADD(MINUTE, -80, GETDATE()) -- Älter als 60 Minuten

)

-- Formatierte Liste der Datenbanken erzeugen

SELECT

    @DatabaseList = STRING_AGG(

        ''Datenbank: '' + database_name +

        '' | Letztes Backup: '' + CONVERT(VARCHAR, LastBackupDate, 120) +

        '' | Alter des letzten Backups in Minuten : '' + CAST(DATEDIFF(MINUTE, LastBackupDate, GETDATE()) AS NVARCHAR),

        CHAR(13) + CHAR(10)

    )

FROM BackupData;



-- Wenn Datenbanken gefunden werden, Alert auslösen

IF @DatabaseList IS NOT NULL

BEGIN

    -- Nachricht mit Server- und Instanznamen sowie Datenbanken vorbereiten

    SET @Message = FORMATMESSAGE(

        ''%sServer: %s%sInstance: %s%s%sVeraltetes Transactionlog-Backup für folgende Datenbanken:%s%s'',

        CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10), @ServerName, CHAR(13) + CHAR(10),

        @InstanceName, CHAR(13) + CHAR(10),

        CHAR(13) + CHAR(10), -- Zusätzliche Leerzeilen

        CHAR(13) + CHAR(10),

        @DatabaseList

    );



    -- Alert auslösen

    RAISERROR (50008, 16, 1, @Message) WITH LOG;

END;

',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Check TransactionLog Backup every 10 Minutes',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=4,

@freq_subday_interval=10,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20250120,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N'61719487-5e69-4109-8abf-d024f3489a02'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO