Dernier backup et dernier restore d'une base, réplicas AlwaysOn compris.

-- Derniers backups par base, sur l'instance où le script est exécuté
-- Droit requis : lecture de msdb. Pour un groupe de disponibilité, voir aussi la partie 3.
SET NOCOUNT ON;

SELECT
    d.name                                    AS base,
    d.state_desc                              AS etat,
    d.recovery_model_desc                     AS modele_recuperation,
    ag.name                                   AS groupe_disponibilite,
    ars.role_desc                             AS role_local,
    CASE WHEN ag.name IS NOT NULL
         THEN sys.fn_hadr_backup_is_preferred_replica(d.name) END AS replica_prefere_backup,
    (SELECT MAX(b.backup_finish_date) FROM msdb.dbo.backupset AS b
      WHERE b.database_name = d.name AND b.server_name = @@SERVERNAME
        AND b.type = 'D' AND b.is_copy_only = 0)  AS dernier_full,
    (SELECT MAX(b.backup_finish_date) FROM msdb.dbo.backupset AS b
      WHERE b.database_name = d.name AND b.server_name = @@SERVERNAME
        AND b.type = 'D' AND b.is_copy_only = 1)  AS dernier_full_copie_seule,
    (SELECT MAX(b.backup_finish_date) FROM msdb.dbo.backupset AS b
      WHERE b.database_name = d.name AND b.server_name = @@SERVERNAME
        AND b.type = 'I')                         AS dernier_differentiel,
    (SELECT MAX(b.backup_finish_date) FROM msdb.dbo.backupset AS b
      WHERE b.database_name = d.name AND b.server_name = @@SERVERNAME
        AND b.type = 'L')                         AS dernier_journal
FROM sys.databases AS d
LEFT JOIN sys.dm_hadr_database_replica_states AS drs
       ON drs.database_id = d.database_id AND drs.is_local = 1
LEFT JOIN sys.availability_groups AS ag
       ON ag.group_id = drs.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
       ON ars.replica_id = drs.replica_id AND ars.is_local = 1
WHERE d.name <> N'tempdb'
ORDER BY d.name;

-- Dernier restore enregistré pour chaque base de cette instance
SET NOCOUNT ON;

;WITH r AS (
    SELECT
        rh.destination_database_name              AS base,
        rh.restore_date                           AS date_restore,
        rh.user_name                              AS utilisateur,
        CASE rh.restore_type WHEN 'D' THEN 'Complet'
                             WHEN 'I' THEN 'Différentiel'
                             WHEN 'L' THEN 'Journal'
                             WHEN 'F' THEN 'Fichier'
                             WHEN 'G' THEN 'Groupe de fichiers'
                             WHEN 'V' THEN 'Vérification seule'
                             ELSE rh.restore_type END AS type_restore,
        bs.server_name                            AS serveur_source,
        bs.database_name                          AS base_source,
        bs.backup_finish_date                     AS sauvegarde_du,
        ROW_NUMBER() OVER (PARTITION BY rh.destination_database_name
                           ORDER BY rh.restore_date DESC) AS rang
    FROM msdb.dbo.restorehistory AS rh
    LEFT JOIN msdb.dbo.backupset AS bs
           ON bs.backup_set_id = rh.backup_set_id
)
SELECT base, date_restore, utilisateur, type_restore, serveur_source, base_source, sauvegarde_du
FROM r
WHERE rang = 1
ORDER BY base;

-- Derniers backups d'une base sur tous les réplicas de son groupe de disponibilité
SET NOCOUNT ON;

DECLARE @Base sysname = N'MaBase';
DECLARE @sql  nvarchar(max) = N'';

-- Réplica local
SET @sql = N'SELECT @@SERVERNAME AS replica, b.database_name AS base, b.type, b.is_copy_only,
                    MAX(b.backup_finish_date) AS dernier_backup
             FROM msdb.dbo.backupset AS b
             WHERE b.database_name = @Base AND b.server_name = @@SERVERNAME
             GROUP BY b.database_name, b.type, b.is_copy_only';

-- Autres réplicas (serveurs liés portant le nom du réplica)
SELECT @sql += N' UNION ALL SELECT ' + QUOTENAME(ar.replica_server_name, '''')
             + N' AS replica, b.database_name, b.type, b.is_copy_only, MAX(b.backup_finish_date)
                FROM ' + QUOTENAME(ar.replica_server_name) + N'.msdb.dbo.backupset AS b
                WHERE b.database_name = @Base AND b.server_name = ' + QUOTENAME(ar.replica_server_name, '''')
             + N' GROUP BY b.database_name, b.type, b.is_copy_only'
FROM sys.availability_replicas AS ar
JOIN sys.availability_databases_cluster AS adc
     ON adc.group_id = ar.group_id AND adc.database_name = @Base
WHERE ar.replica_server_name <> @@SERVERNAME;

SET @sql = N'SELECT replica, base,
                    CASE type WHEN ''D'' THEN CASE WHEN is_copy_only = 1 THEN ''Full (copie seule)'' ELSE ''Full'' END
                              WHEN ''I'' THEN ''Différentiel''
                              WHEN ''L'' THEN ''Journal'' END AS type_backup,
                    dernier_backup
             FROM (' + @sql + N') AS t
             WHERE type IN (''D'', ''I'', ''L'')
             ORDER BY base, type, replica';

EXEC sys.sp_executesql @sql, N'@Base sysname', @Base = @Base;