Início / dba / Perguntas / 345893
Accepted
Marcello Miorelli
Asked: 2025-03-26 19:48:13 +0800 CST

consulta para encontrar o backup transacional mais recente para um servidor envolvido no grupo de disponibilidade

  • 772

Recebi uma solicitação para encontrar o backup de log transacional mais recente. Neste servidor específico, ele não está funcionando por algum motivo.

aqui está a pergunta:

SELECT
    @@Servername AS [Server_Name],
    B.name AS Database_Name,
    ISNULL(STR(ABS(DATEDIFF(day, SYSDATETIME(), MAX(A.backup_finish_date)))), 'NEVER') AS DaysSinceLastBackup,
    ISNULL(CONVERT(CHAR(11), MAX(A.backup_finish_date), 113) + ' ' + CONVERT(VARCHAR(8), MAX(A.backup_finish_date), 108), 'NEVER') AS LastBackupDate,
    BackupSize_GB = REPLACE(CONVERT(VARCHAR(50), CAST(CAST(COALESCE(MAX(A.compressed_backup_size), MAX(A.backup_size), 0)/1024.0/1024.0/1024.0 AS NUMERIC(18,2)) AS MONEY),1), '.00',''),
    BackupSize_MB = REPLACE(CONVERT(VARCHAR(50), CAST(CAST(COALESCE(MAX(A.compressed_backup_size), MAX(A.backup_size), 0)/1024.0/1024.0 AS NUMERIC(18,2)) AS MONEY),1), '.00',''),
    [Last Backup Duration (sec)] = DATEDIFF(s, MAX(A.backup_start_date), MAX(A.backup_finish_date)),
    [AVG Backup Duration (sec)] = AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS INT)),
    [Longest Backup Duration (sec)] = MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS INT)),
    [Shortest Backup Duration (sec)] = MIN(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS INT)),
    A.type,
    media_set_id = MAX(A.media_set_id),
    B.create_date,
    B.recovery_model_desc,
    B.state_desc,
    B.is_read_only,
    B.database_id,
    Backup_Started = MAX(A.backup_start_date),
    Backup_Finished = MAX(A.backup_finish_date)
FROM sys.databases B WITH(NOLOCK)
LEFT OUTER JOIN msdb.dbo.backupset A WITH(NOLOCK)
    ON A.database_name = B.name
    AND A.type = 'L' -- Solo Transaction Log
WHERE 1=1
--B.Name = 'My_database'
GROUP BY
    B.Name,
    B.database_id,
    B.create_date,
    B.recovery_model_desc,
    B.state_desc,
    B.is_read_only,
    A.type
sql-server

1 respostas

  1. Best Answer

    Devemos consultar o AG e obter qual é o servidor preferencial para os backups:

    -- Get Availability Group and Replica Info
SELECT 
  ServerName = @@SERVERNAME,
  AG.name AS AG_Name,
  AR.replica_server_name,
  ARS.role_desc,
  CASE AG.automated_backup_preference
     WHEN 0 THEN 'Primary'
     WHEN 1 THEN 'Secondary only'
     WHEN 2 THEN 'Prefer Secondary'
     WHEN 3 THEN 'Any Replica'
  END AS backup_preference
FROM sys.availability_groups AG
JOIN sys.availability_replicas AR ON AG.group_id = AR.group_id
JOIN sys.dm_hadr_availability_replica_states ARS ON AR.replica_id = ARS.replica_id
-- WHERE AR.replica_server_name = @@SERVERNAME -- Uncomment if needed

    neste exemplo neste servidor em particular: insira a descrição da imagem aqui

    No entanto, depois de saber, você pode executar a consulta original da pergunta naquele servidor.

    Por exemplo, usando openrowset: (aplicam-se as configurações de conectividade e configuração)

    
DECLARE @PreferredServer NVARCHAR(128)

SELECT TOP 1 @PreferredServer = AR.replica_server_name
FROM sys.availability_groups AG
JOIN sys.availability_replicas AR ON AG.group_id = AR.group_id
WHERE AG.automated_backup_preference = 0 -- Adjust based on your logic

-- Dynamic SQL using OPENROWSET
DECLARE @sql NVARCHAR(MAX) = '
SELECT * FROM OPENROWSET(''SQLNCLI'', 
    ''Server=' + @PreferredServer + ';Trusted_Connection=yes;'', 
    ''select @@servername'')' /*this is where the original query would be*/

EXEC(@sql)
    • 0

relate perguntas