AlwaysOn--查看可用性組的首先備份節點

在Alwayson中，可以通過設置來將備份放到指定的節點上完成，微軟提供了函數用來判斷當前指定節點進行備份：

DECLARE @database_name NVARCHAR(200)
SET @database_name= 'DB5'
SELECT
CASE [master].[sys].[fn_hadr_backup_is_preferred_replica](@database_name)
WHEN 1 THEN '可以在當前節點進行備份'
ELSE '不可以在當前節點進行備份'
END

當DBA登錄到主節點上時，如果希望查看當前可用性組下那個節點是首先備份節點，需要是打開Alwasyon的屬性界面進行查看，很是不方便，於是有了下麵這段代碼：

-------------------------------------------------------
--查看AG上首選備份節點

IF(OBJECT_ID('tempdb..#tb_replica_server')IS NOT NULL)
BEGIN
 DROP TABLE #tb_replica_server
END

CREATE TABLE #tb_replica_server
(replica_server_name NVARCHAR(200))

--查看AG上備份設置和AG組名
DECLARE @backup_preference INT
DECLARE @availability_group_name NVARCHAR(200)

SELECT 
@backup_preference =AG.[automated_backup_preference],
@availability_group_name= AG.[name]
FROM sys.availability_groups AG

IF(@availability_group_name IS NOT NULL)
BEGIN

    --查看AG上的節點數量
    DECLARE @replica_server_count INT
    SELECT @replica_server_count=COUNT(1) 
    FROM sys.availability_groups AG
    INNER JOIN sys.availability_replicas AR
    ON AR.group_id = AG.group_id
    WHERE  AG.[name]= @availability_group_name

    --@backup_preference= 1 僅主節點備份
    IF @backup_preference= 0
    BEGIN
        INSERT INTO #tb_replica_server(replica_server_name)
        SELECT AR.replica_server_name FROM sys.availability_groups AG
        INNER JOIN sys.availability_replicas AR
        ON AR.group_id = AG.group_id
        INNER JOIN sys.[dm_hadr_availability_replica_states] AGS
        ON AGS.group_id = AR.group_id
        AND AGS.replica_id = AR.replica_id
        WHERE AG.[name] = @availability_group_name
        AND AGS.role=1
    END


    --@backup_preference= 1 僅輔助備份
    --@backup_preference=2  首先輔助副本
    IF (@backup_preference= 1 
        OR(@backup_preference=2 AND @replica_server_count>1))
    BEGIN
        INSERT INTO #tb_replica_server(replica_server_name)
        SELECT T1.replica_server_name FROM (
        SELECT AR.replica_server_name,
        ROW_NUMBER()OVER(ORDER BY AR.backup_priority DESC) AS RID
        FROM sys.availability_groups AG
        INNER JOIN sys.availability_replicas AR
        ON AR.group_id = AG.group_id
        INNER JOIN sys.[dm_hadr_availability_replica_states] AGS
        ON AGS.group_id = AR.group_id
        AND AGS.replica_id = AR.replica_id
        WHERE AG.[name] = @availability_group_name
        AND AGS.role=2
        ) AS T1
        WHERE T1.RID=1
    END

    --首先輔助副本但只有主節點
    IF (@backup_preference=2 AND @replica_server_count=1)
    BEGIN
        INSERT INTO #tb_replica_server(replica_server_name)
        SELECT AR.replica_server_name FROM sys.availability_groups AG
        INNER JOIN sys.availability_replicas AR
        ON AR.group_id = AG.group_id
        INNER JOIN sys.[dm_hadr_availability_replica_states] AGS
        ON AGS.group_id = AR.group_id
        AND AGS.replica_id = AR.replica_id
        WHERE AG.[name] = @availability_group_name
        AND AGS.role=1
    END

    --任意備份節點
    IF (@backup_preference=3)
    BEGIN
        INSERT INTO #tb_replica_server(replica_server_name)
        SELECT T1.replica_server_name FROM (
        SELECT AR.replica_server_name,
        ROW_NUMBER()OVER(ORDER BY AR.backup_priority DESC) AS RID
        FROM sys.availability_groups AG
        INNER JOIN sys.availability_replicas AR
        ON AR.group_id = AG.group_id
        INNER JOIN sys.[dm_hadr_availability_replica_states] AGS
        ON AGS.group_id = AR.group_id
        AND AGS.replica_id = AR.replica_id
        WHERE AG.[name] = @availability_group_name
        ) AS T1
        WHERE T1.RID=1
    END

    SELECT '當前可用組為：'+@availability_group_name
    SELECT '首先備份節點為：',T1.replica_server_name
    FROM #tb_replica_server T1

END
ELSE
BEGIN
    SELECT '當前不存在可用性組'
END

有需要的朋友可以進行簡單封裝成函數來使用。

找到備份節點，下麵腳本可供您方便查看資料庫備份情況

查詢指定資料庫最近一天的備份歷史記錄：

--======================================
--查詢指定資料庫最近一天的備份歷史記錄
SELECT
CONVERT(CHAR(100),SERVERPROPERTY('Servername'))ASServer,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.expiration_date,
CASE bs.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END ASbackup_type,
bs.backup_size,
bmf.logical_device_name,
bmf.physical_device_name, 
bs.name AS backupset_name,
bs.description,
'RESTORE DATABASE [DatabaseName] FROM DISK=N'''
+bmf.physical_device_name+ '''WITH NORECOVERY;'
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs
ON bmf.media_set_id=bs.media_set_id
WHERE bs.database_name='DatabaseName'
AND bs.backup_start_date>DATEADD(DAY,-1,GETDATE())
ORDER BY bs.database_name,
bs.backup_finish_date

查看實例上各資料庫最後一次備份時間

--====================================
--查看資料庫最後一次備份時間
WITH T1 AS(
SELECT  B.[database_name] ,
        B.[type],
        B.[backup_finish_date] AS [LastBackupDate] ,
        BMS.[physical_device_name] ,
        ROW_NUMBER() OVER ( PARTITION BY B.[database_name], B.[type] 
        ORDER BY B.[backup_finish_date] DESC ) AS RID
FROM    [msdb]..[backupset] B WITH(NOLOCK)
        INNER JOIN [msdb]..[backupmediafamily] BMS WITH(NOLOCK)
        ON BMS.[media_set_id] = B.[media_set_id]
),T2 AS
(
    SELECT 
    [database_name],
    [type],
    [LastBackupDate],
    [physical_device_name] 
    FROM T1
    WHERE T1.RID=1
)
SELECT  DB.[name] AS [DatabaseName],
        DB.[recovery_model_desc] AS [RecoveryModel],
        F.[LastBackupDate] AS [LastFullBackupDate],
        F.[physical_device_name] AS [LastFullBackupFile],
        D.[LastBackupDate] AS [LastDiffBackupDate],
        D.[physical_device_name] AS [LastDiffBackupFile],
        L.[LastBackupDate] AS [LastLogBackupDate],
        L.[physical_device_name] AS [LastLogBackupFile]
FROM    SYS.databases DB WITH(NOLOCK)
LEFT JOIN (SELECT *　FROM T2 
            WHERE  T2.[type]='D') AS F
ON F.[database_name]=DB.[name]
LEFT JOIN (SELECT *　FROM T2 
            WHERE  T2.[type]='I') AS D
ON D.[database_name]=DB.[name]
LEFT JOIN (SELECT *　FROM T2 
            WHERE  T2.[type]='L') AS L
ON L.[database_name]=DB.[name]

運行效果：

--=====================================

妹子壓貼