/* Letztes Backup pro DB + Typ (FULL/DIFF/LOG), inkl. AlwaysOn-Handling (Primary/Local) */

DECLARE @IsHadrEnabled bit = CASE WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 1 ELSE 0 END;

IF @IsHadrEnabled = 0
BEGIN
    SELECT
        last_backup_date = CONVERT(varchar(19), MAX(bs.backup_finish_date), 120),
        bs.type,                               -- D=FULL, I=DIFF, L=LOG
        bs.machine_name,
        is_primary_replica = CAST(1 AS bit),
        is_local           = CAST(1 AS bit),
        replica_id         = CAST(NULL AS uniqueidentifier),
        bs.database_name
    FROM msdb.dbo.backupset bs
    WHERE UPPER(bs.machine_name) = UPPER(CAST(SERVERPROPERTY('MachineName') AS varchar(128)))
    GROUP BY bs.type, bs.machine_name, bs.database_name
    ORDER BY bs.database_name, bs.type;
END
ELSE
BEGIN
    SELECT
        last_backup_date = CONVERT(varchar(19), MAX(b.backup_finish_date), 120),
        b.type,                                -- D=FULL, I=DIFF, L=LOG
        b.machine_name,
        is_primary_replica = ISNULL(rep.is_primary_replica, 0),
        is_local           = ISNULL(rep.is_local, 1),
        replica_id         = rep.replica_id,
        b.database_name
    FROM msdb.dbo.backupset b
    LEFT JOIN sys.databases db
           ON b.database_name = db.name
    LEFT JOIN sys.dm_hadr_database_replica_states rep
           ON db.database_id = rep.database_id
    WHERE (rep.is_local IS NULL OR rep.is_local = 1)
      AND (rep.is_primary_replica IS NULL OR rep.is_primary_replica = 1)
      AND UPPER(b.machine_name) = UPPER(CAST(SERVERPROPERTY('MachineName') AS varchar(128)))
    GROUP BY b.type, b.machine_name, rep.is_primary_replica, rep.is_local, rep.replica_id, b.database_name
    ORDER BY b.database_name, b.type;
END