主页 / dba / 问题 / 345893
Accepted
Marcello Miorelli
Asked: 2025-03-26 19:48:13 +0800 CST

查询以查找可用性组中涉及的服务器的最新事务备份

  • 772

我收到一个查询，要求查找最新的事务日志备份。但由于某种原因，它在此特定服务器中不起作用。

查询如下：

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 个回答

  1. Best Answer

    我们应该查询 AG 并获取备份的首选服务器：

    -- 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

    在此示例中，在这个特定的服务器中： 在此处输入图片描述

    但是，一旦您知道了，您就可以在该服务器中运行问题的原始查询。

    例如使用 openrowset：（适用连接和配置设置）

    
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

相关问题