在Alwayson中，可以通过设置来将备份放到指定的节点上完成，微软提供了函数用来判断当前指定节点进行备份：

DECLARE @database_name NVARCHAR(200)SET @database_name= 'DB5'SELECTCASE [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_serverENDCREATE TABLE #tb_replica_server
(replica_server_name NVARCHAR(200))--查看AG上备份设置和AG组名DECLARE @backup_preference INTDECLARE @availability_group_name NVARCHAR(200)SELECT @backup_preference =AG.[automated_backup_preference],@availability_group_name= AG.[name]FROM sys.availability_groups AGIF(@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= 
    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 T1ENDELSEBEGIN
    SELECT '当前不存在可用性组'END

有需要的朋友可以进行简单封装成函数来使用。

找到备份节点，下面脚本可供您方便查看数据库备份情况

查询指定数据库最近一天的备份历史记录：

--======================================--查询指定数据库最近一天的备份历史记录SELECTCONVERT(CHAR(100),SERVERPROPERTY('Servername'))ASServer,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.expiration_date,CASE bs.typeWHEN '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 bmfINNER JOIN msdb.dbo.backupset bsON bmf.media_set_id=bs.media_set_idWHERE 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 RIDFROM    [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 FON F.[database_name]=DB.[name]LEFT JOIN (SELECT *　FROM T2 
            WHERE  T2.[type]='I') AS DON D.[database_name]=DB.[name]LEFT JOIN (SELECT *　FROM T2 
            WHERE  T2.[type]='L') AS LON L.[database_name]=DB.[name]

运行效果：

更多SQL Server文章请关注

ITPub博客

AlwaysOn--查看可用性组的首先备份节点

最新文章