SQL Server 在AlwaysOn环境中收集最后的备份信息

如果您已经使用过部分备份和只读文件组备份，那么您会知道备份顺序非常重要，但是如果您不这样做，则需要恢复时会很快注意到它，并且您可以很容易地理解为什么这最后一个备份信息至关重要。由于备份始终必须在主副本上运行，因此，如果发生故障转移并且更改了主副本，则必须收集所有副本上的信息，以确保您在正确的时间执行正确的备份，而不进行不必要的备份（记住数据量） 。

解决方案和代码说明

还要提到的另一件事是，由于安全策略的缘故，禁止使用链接服务器，但希望可以使用xp_CmdShell。我希望每个副本独立工作，并且需要一种查询远程副本以收集有关每个SQL Server实例的最新备份信息的方法。由于备份历史记录可能很干净，因此我需要将此信息存储在本地表中。我创建了2个表，一个表用于存储最后一个数据库备份信息，另一个表用于存储最后一个只读文件组备份信息。另外，我创建了2个表来临时收集来自所有副本的信息。

创建最后一个备份信息表：

--########################################################
--###Backup generator - backup last date info temporary table
--########################################################
 
USE [<YourDatabaseName>]
GO
/*
if OBJECT_ID('[dbo].[bakgen_backuplastdt_databases_temp]') is not null
    drop table [dbo].[bakgen_backuplastdt_databases_temp]
*/
create table [dbo].[bakgen_backuplastdt_databases_temp] (
    ServerName sysname not null,
    SqlInstanceName sysname  not null,
    SqlServerName sysname  not null,
    DatabaseCreationDate datetime  not null,
    DatabaseName sysname  not null,
    BackupType char(1) not null,
    LastBackupDate datetime  not null,
    LastBackupSize numeric(20,0) not null,
    is_primary bit null,
    insertdate datetime  not null
)
GO
create unique clustered index idx_bakgen_backuplastdt_databases_temp on [dbo].[bakgen_backuplastdt_databases_temp](DatabaseCreationDate,DatabaseName,BackupType,ServerName,SqlInstanceName)
 
 
 
--########################################################
--###Backup generator - backup last date info
--########################################################
 
USE [<YourDatabaseName>]
GO
/*
if OBJECT_ID('[dbo].[bakgen_backuplastdt_databases]') is not null
    drop table [dbo].[bakgen_backuplastdt_databases]
*/
create table [dbo].[bakgen_backuplastdt_databases] (
    ServerName sysname  not null,
    SqlInstanceName sysname  not null,
    SqlServerName sysname  not null,
    DatabaseCreationDate datetime  not null,
    DatabaseName sysname  not null,
    BackupType char(1) not null,
    LastBackupDate datetime  not null,
    LastBackupSize numeric(20,0) not null,
    is_primary bit null,
    insertdate datetime  not null
)
GO
create unique clustered index idx_bakgen_backuplastdt_databases on [dbo].[bakgen_backuplastdt_databases](DatabaseCreationDate,DatabaseName,BackupType,ServerName,SqlInstanceName)

最终决定使用存储过程来调用PowerShell脚本，以远程执行副本上的查询。
该存储过程列出了现有副本并收集了最后的数据库备份信息，然后该只读文件组备份信息创建了两个不同的查询，以在服务器上本地执行并将数据首先存储在临时表中。它将创建类似的查询，排除不包括在可用性组中的数据库，并使用运行PowerShell脚本的xp_CmdShell在远程副本上执行它们。使用生成的TSQL查询动态创建PowerShell脚本。他们使用了著名的DBATools的一项功能。因此，您必须先安装它。
您会注意到，为了记录日志，生成的脚本经过了很好的格式化，以便于阅读和调试它们。但是在通过xp_CmdShell执行PowerShell脚本之前，您需要应用一些字符串格式，例如我添加的两行内容，以避免执行失败：

设置@PSCmd = replace（replace（@PSCmd，nchar（13），N“），nchar（10），N’’）
设置@PSCmd = replace（@PSCmd，’>’，N’^>’）

不要忘记转义一些字符，否则执行将失败，在我的情况下，在xp_CmdShell执行的输出中省略转义’>'符号会引发“ Access is denied”（拒绝访问）消息。

之后，代码会将临时表中已收集的内容与最终信息进行比较，并在需要时更新信息。

这是存储过程的完整代码：

use [<YourDatabaseName>]
if OBJECT_ID('dbo.bakgen_p_getbakinfo') is not null
    drop procedure dbo.bakgen_p_getbakinfo 
go
 
CREATE PROCEDURE dbo.bakgen_p_getbakinfo 
AS
/************************************
*   dbi-services SA, Switzerland    *
*   http://www.dbi-services.com        *
*************************************
    Group/Privileges..: DBA
    Script Name......:  bakgen_p_getbakinfo.sql
    Author...........:  Christophe Cosme
    Date.............:  2019-09-20
    Version..........:  SQL Server 2016 / 2017
    Description......:  Get the backup information locally but also on the replica involved
 
    Input parameters.: 
 
    Output parameter: 
                 
************************************************************************************************
    Historical
    Date        Version    Who    Whats     Comments
    ----------  -------    ---    --------  -----------------------------------------------------
    2019-09-30  1.0        CHC    Creation
************************************************************************************************/ 
BEGIN
 
BEGIN TRY
     
    set nocount on
 
    declare
    @ErrorMessage  NVARCHAR(4000), 
    @ErrorSeverity INT, 
    @ErrorState    INT;
 
    declare @ModuleName sysname,
            @ProcName sysname,
            @InfoLog nvarchar(max),
            @Execute char(1)
         
    set @ModuleName = 'BakGen'
    set @ProcName = OBJECT_NAME(@@PROCID)
    set @Execute = 'A'
 
    set @InfoLog = 'Retrieve backup information'
    execute dbo.bakgen_p_log    
        @ModuleName = @ModuleName,
        @ProcedureName = @ProcName,
        @ExecuteMode = @Execute,
        @LogType = 'INFO',
        @DatabaseName = null,
        @Information = @InfoLog,
        @Script = null
 
 
    --###variable to store error message
    declare @errmsg varchar(4000)
    --###variable with the current datetime
    declare @cdt datetime = getdate()
 
    --###variabler to store the sql and powershell commands to execute
    declare @sqllocalDB nvarchar(4000),
            @sqllocalFG nvarchar(4000),
            @sqlremoteDB nvarchar(4000),
            @sqlremoteFG nvarchar(4000),
            @PSCmd nvarchar(4000)
 
    --###variable to store the local SQL server name
    declare @LocalSqlServerName sysname
    --###variable to store the list of replicas
    declare @TAgReplica table (AgReplicaName sysname)
    --###variable for the cursors
    declare @AgReplicaName sysname
 
    --###set the local SQL Server name
    set @LocalSqlServerName = lower(convert(sysname,serverproperty('ServerName')))
         
 
    --############################################################################
    --### check if tables exist
    --############################################################################
    if object_id('[dbo].[bakgen_backuplastdt_databases_temp]') is null
    begin
        set @errmsg = 'Get Backup info : table not found'
        set @errmsg += '    table name = [dbo].[bakgen_backuplastdt_databases_temp]'
        raiserror (@errmsg,11,1);
    end
    if object_id('[dbo].[bakgen_backuplastdt_fgreadonly_temp]') is null
    begin
        set @errmsg = 'Get Backup info : table not found'
        set @errmsg += '    table name = [dbo].[bakgen_backuplastdt_fgreadonly_temp]'
        raiserror (@errmsg,11,1);       
    end
 
    if object_id('[dbo].[bakgen_backuplastdt_databases]') is null
    begin
        set @errmsg = 'Get Backup info : table not found'
        set @errmsg += '    table name = [dbo].[bakgen_backuplastdt_databases]'
        raiserror (@errmsg,11,1);
    end
    if object_id('[dbo].[bakgen_backuplastdt_fgreadonly]') is null
    begin
        set @errmsg = 'Get Backup info : table not found'
        set @errmsg += '    table name = [dbo].[bakgen_backuplastdt_fgreadonly]'
        raiserror (@errmsg,11,1);       
    end
 
 
     
    --############################################################################
    --### select the replicas involved adding first the local server
    --############################################################################
    insert into @TAgReplica (AgReplicaName ) select @LocalSqlServerName
 
    --###check if alwayson feature is activated
    if (serverproperty('IsHadrEnabled') = 1)
    begin
        insert into @TAgReplica (AgReplicaName )
        select lower(agr.replica_server_name) from sys.availability_replicas agr
            where agr.replica_server_name <> @LocalSqlServerName
    end
 
 
    --############################################################################
    --### construct the SQL command to execute on the local SQL Server
    --############################################################################
    set @sqllocalDB = ''
    set @sqllocalDB +='
 
    declare @Tbi table (
        ServerName sysname,
        SqlInstanceName sysname,
        SqlServerName sysname,
        DatabaseCreationDate datetime,
        DatabaseName sysname,
        BackupType char(1),
        LastBackupDate datetime,
        is_primary bit null,
        insertdate datetime 
    )
 
 
    insert into @Tbi (
        [ServerName],
        [SqlInstanceName],
        [SqlServerName],
        [DatabaseCreationDate],
        [DatabaseName],
        [BackupType],
        [LastBackupDate],
        [is_primary],
        [insertdate])
    select  
        lower(convert(sysname,serverproperty(''machinename''))) as ServerName,
        lower(convert(sysname,serverproperty(''InstanceName''))) as SqlInstanceName,
        lower(convert(sysname,serverproperty(''ServerName''))) as SqlServerName,
        db.create_date as DatabaseCreationDate,
        bs.database_name as DatabaseName,
        bs.type as BackupType,
        max(bs.backup_finish_date) as LastBackupDate,
        sys.fn_hadr_is_primary_replica(bs.database_name) as is_primary,
        ''' + convert(varchar,@cdt,120) + '''   
    from msdb.dbo.backupset bs
        inner join sys.databases db on db.name = bs.database_name
        where bs.type in (''D'',''I'',''P'',''Q'')
            and bs.is_copy_only = 0
            and coalesce(sys.fn_hadr_is_primary_replica(bs.database_name),-1) in (-1,0,1)
        group by
            db.create_date,
            bs.database_name,
            bs.type, 
            sys.fn_hadr_is_primary_replica(bs.database_name)
 
    insert into [dbo].[bakgen_backuplastdt_databases_temp] (
        [ServerName],
        [SqlInstanceName],
        [SqlServerName],
        [DatabaseCreationDate],
        [DatabaseName],
        [BackupType],
        [LastBackupDate],
        [LastBackupSize],
        [is_primary],
        [insertdate])
    select  
        t.[ServerName],
        t.[SqlInstanceName],
        t.[SqlServerName],
        t.[DatabaseCreationDate],
        t.[DatabaseName],
        t.[BackupType],
        t.[LastBackupDate],
        bs.[backup_size],
        t.[is_primary],
        t.[insertdate]
    from @Tbi t
        inner join msdb.dbo.backupset bs on 
            bs.backup_finish_date = t.LastBackupDate  
            and bs.database_name collate database_default = t.DatabaseName collate database_default
            and bs.type collate database_default = t.BackupType collate database_default
'
 
 
 
 
    set @sqllocalFG = ''
    set @sqllocalFG +='
 
    insert into [dbo].[bakgen_backuplastdt_fgreadonly_temp]
           ([ServerName],
           [SqlInstanceName],
           [SqlServerName],
           [DatabaseCreationDate],
           [DatabaseName],
           [BackupType],
           [filegroup_name],
           [file_logicalname],
           [filegroup_guid],
           [file_guid],
           [LastBackupDate],
           [LastBackupReadOnlyLsn],
           [is_primary],
           [insertdate])
    select  
        lower(convert(sysname,serverproperty(''machinename''))) as ServerName,
        lower(convert(sysname,serverproperty(''InstanceName''))) as SqlInstanceName,
        lower(convert(sysname,serverproperty(''ServerName''))) as SqlServerName,
        db.create_date as DatabaseCreationDate,
        bs.database_name as DatabaseName,
        bs.type as BackupType,
        bf.filegroup_name,
        bf.logical_name as file_logicalname,
        bf.filegroup_guid,
        bf.file_guid,
        max(bs.backup_finish_date) as LastBackupDate,
        max(bf.read_only_lsn) as LastBackupReadOnlyLsn,
        sys.fn_hadr_is_primary_replica(bs.database_name) as is_primary, 
        ''' + convert(varchar,@cdt,120) + '''   
    from msdb.dbo.backupset bs
            inner join msdb.dbo.backupfile bf on  bf.backup_set_id = bs.backup_set_id
            inner join sys.databases db on db.name = bs.database_name 
        where 
            bs.backup_finish_date >= db.create_date 
            and bs.type in (''F'')
            and bs.is_copy_only = 0
            and coalesce(sys.fn_hadr_is_primary_replica(bs.database_name),-1) in (-1,0,1)
            and bf.is_present = 1
            and bf.is_readonly = 1
            and bf.file_type = ''D''
        group by
        db.create_date,
        bs.database_name, 
        bs.type,
        bf.filegroup_name,
        bf.logical_name, 
        bf.filegroup_guid,
        bf.file_guid,
        sys.fn_hadr_is_primary_replica(bs.database_name)
'
 
 
     
    --############################################################################
    --### construct the SQL command to execute on the remote SQL Server
    --############################################################################
    set @sqlremoteDB = ''
    set @sqlremoteDB +='
 
    declare @Tbi table (
        ServerName sysname,
        SqlInstanceName sysname,
        SqlServerName sysname,
        DatabaseCreationDate datetime, 
        DatabaseName sysname,
        BackupType char(1),
        LastBackupDate datetime,
        is_primary bit null,
        insertdate datetime 
    )
 
    insert into @Tbi (
        [ServerName],
        [SqlInstanceName],
        [SqlServerName],
        [DatabaseCreationDate],
        [DatabaseName],
        [BackupType],
        [LastBackupDate],
        [is_primary],
        [insertdate])
    select  
        lower(convert(sysname,serverproperty(''machinename''))) as ServerName,
        lower(convert(sysname,serverproperty(''InstanceName''))) as SqlInstanceName,
        lower(convert(sysname,serverproperty(''ServerName''))) as SqlServerName,
        db.create_date as DatabaseCreationDate,
        bs.database_name as DatabaseName,
        bs.type as BackupType,
        max(bs.backup_finish_date) as LastBackupDate,
        sys.fn_hadr_is_primary_replica(bs.database_name) as is_primary, 
        ''' + convert(varchar,@cdt,120) + '''     
    from msdb.dbo.backupset bs
        inner join sys.databases db on db.name = bs.database_name 
        where bs.type in (''D'',''I'',''P'',''Q'')
            and bs.is_copy_only = 0
            and coalesce(sys.fn_hadr_is_primary_replica(bs.database_name),-1) in (0,1)
        group by
            db.create_date,
            bs.database_name,
            bs.type,
            sys.fn_hadr_is_primary_replica(bs.database_name) 
 
    select  
        t.[ServerName],
        t.[SqlInstanceName],
        t.[SqlServerName],
        t.[DatabaseCreationDate],
        t.[DatabaseName],
        t.[BackupType],
        t.[LastBackupDate],
        bs.[backup_size],
        t.[is_primary],
        t.[insertdate]
    from @Tbi t
        inner join msdb.dbo.backupset bs on 
            bs.backup_finish_date = t.LastBackupDate 
            and bs.database_name collate database_default = t.DatabaseName collate database_default
            and bs.type collate database_default = t.BackupType collate database_default
 
'
 
    set @sqlremoteFG = ''
    set @sqlremoteFG +='
 
    select  
        lower(convert(sysname,serverproperty(''machinename''))) as ServerName,
        lower(convert(sysname,serverproperty(''InstanceName''))) as SqlInstanceName,
        lower(convert(sysname,serverproperty(''ServerName''))) as SqlServerName,
        db.create_date as DatabaseCreationDate,
        bs.database_name as DatabaseName,
        bs.type as BackupType,
        bf.filegroup_name,
        bf.logical_name as file_logicalname,
        bf.filegroup_guid,
        bf.file_guid,
        max(bs.backup_finish_date) as LastBackupDate,
        max(bf.read_only_lsn) as LastReadOnlyLsn,
        sys.fn_hadr_is_primary_replica(bs.database_name) as is_primary, 
        ''' + convert(varchar,@cdt,120) + '''   
    from msdb.dbo.backupset bs
            inner join msdb.dbo.backupfile bf on  bf.backup_set_id = bs.backup_set_id
            inner join sys.databases db on db.name = bs.database_name 
        where 
            bs.backup_finish_date >= db.create_date 
            and bs.type in (''F'')
            and bs.is_copy_only = 0
            and coalesce(sys.fn_hadr_is_primary_replica(bs.database_name),-1) in (0,1)
            and bf.is_present = 1
            and bf.is_readonly = 1
            and bf.file_type = ''D''
        group by
        db.create_date, 
        bs.database_name, 
        bs.type,
        bf.filegroup_name,
        bf.logical_name, 
        bf.filegroup_guid,
        bf.file_guid,
        sys.fn_hadr_is_primary_replica(bs.database_name) 
'
 
    --############################################################################
    --### delete all records in the backup info tables
    --############################################################################
    delete from [dbo].[bakgen_backuplastdt_databases_temp]
    delete from [dbo].[bakgen_backuplastdt_fgreadonly_temp]
 
    --############################################################################
    --### loop for all replicas involved
    --############################################################################
    declare cur_replica cursor
    static local forward_only
    for
        select AgReplicaName
        from @TAgReplica
                  
    open cur_replica
    fetch next from cur_replica into
        @AgReplicaName      
 
 
    while @@fetch_status = 0
    begin
             
        if @LocalSqlServerName = @AgReplicaName
        begin
 
            set @InfoLog = 'Get database backup information on local SQL Server instance ' + QUOTENAME(@AgReplicaName)
            execute dbo.bakgen_p_log    
                @ModuleName = @ModuleName,
                @ProcedureName = @ProcName,
                @ExecuteMode = @Execute,
                @LogType = 'INFO',
                @DatabaseName = null,
                @Information = @InfoLog,
                @Script = @sqllocalDB
            execute sp_executesql @sqllocalDB
 
            set @InfoLog = 'Get read-only filegroup backup information on local SQL Server instance ' + QUOTENAME(@AgReplicaName)
            execute dbo.bakgen_p_log    
                @ModuleName = @ModuleName,
                @ProcedureName = @ProcName,
                @ExecuteMode = @Execute,
                @LogType = 'INFO',
                @DatabaseName = null,
                @Information = @InfoLog,
                @Script = @sqllocalFG
            execute sp_executesql @sqllocalFG
 
        end
        else
        begin
            --############################################################################
            --### construct the PowerShell command to execute on the remote SQL Server
            --############################################################################
            set @PSCmd  = ''
            set @PSCmd += 'PowerShell.exe '
            set @PSCmd += '-Command "'
            set @PSCmd += '$qrydb = \"' + @sqlremoteDB + '\"; '
            set @PSCmd += '$qryfg = \"' + @sqlremoteFG + '\"; '
            set @PSCmd += '$rdb = Invoke-DbaQuery -SqlInstance ' + @AgReplicaName + ' -Query $qrydb; '
            set @PSCmd += '$rfg = Invoke-DbaQuery -SqlInstance ' + @AgReplicaName + ' -Query $qryfg; '
            set @PSCmd += 'if ($rdb -ne $null) { '
            set @PSCmd += 'Write-DbaDbTableData -SqlInstance ' + @LocalSqlServerName + ' -Database ' + db_name() + ' -Schema dbo -Table bakgen_backuplastdt_databases_temp -InputObject $rdb;'
            set @PSCmd += '} '
            set @PSCmd += 'if ($rfg -ne $null) { '
            set @PSCmd += 'Write-DbaDbTableData -SqlInstance ' + @LocalSqlServerName + ' -Database ' + db_name() + ' -Schema dbo -Table bakgen_backuplastdt_fgreadonly_temp -InputObject $rfg;'
            set @PSCmd += '} '
            set @PSCmd += '"'
 
            set @InfoLog = 'Get backup information on replica SQL Server instance ' + QUOTENAME(@AgReplicaName) + ' executing master..xp_cmdshell PowerShell script'
            execute dbo.bakgen_p_log    
                @ModuleName = @ModuleName,
                @ProcedureName = @ProcName,
                @ExecuteMode = @Execute,
                @LogType = 'INFO',
                @DatabaseName = null,
                @Information = @InfoLog,
                @Script = @PSCmd
 
            --###remove CRLF for xp_cmdshell and PowerShell 
            set @PSCmd = replace(replace(@PSCmd, nchar(13), N''), nchar(10), N' ')
            set @PSCmd = replace(@PSCmd, '>', N'^>')
            --###Execute the powershell command on the replica and store the result in the temporary tables
            exec master..xp_cmdshell @PSCmd
        end
         
        fetch next from cur_replica into
            @AgReplicaName      
 
 
    end
    close cur_replica
    deallocate cur_replica
 
 
    --############################################################################
    --### Update and insert backup information in final tables
    --############################################################################
    BEGIN TRY
 
        begin transaction
 
        delete f
            from [dbo].[bakgen_backuplastdt_databases_temp] t
                inner join [dbo].[bakgen_backuplastdt_databases] f 
                    on f.DatabaseCreationDate = t.DatabaseCreationDate
                        and f.DatabaseName = t.DatabaseName 
                        and f.BackupType = t.BackupType 
                        and f.ServerName = t.ServerName 
                        and t.SqlInstanceName = f.SqlInstanceName
                where f.LastBackupDate < t.LastBackupDate
 
        Insert into [dbo].[bakgen_backuplastdt_databases] (
            ServerName,
            SqlInstanceName,
            SqlServerName,
            DatabaseCreationDate,
            DatabaseName,
            BackupType,
            LastBackupDate,
            LastBackupSize,
            is_primary,
            insertdate 
        )
        select
            t.ServerName,
            t.SqlInstanceName,
            t.SqlServerName,
            t.DatabaseCreationDate,
            t.DatabaseName,
            t.BackupType,
            t.LastBackupDate,
            t.LastBackupSize,
            t.is_primary,
            t.insertdate 
            from [dbo].[bakgen_backuplastdt_databases_temp] t
                where not exists (select 1 from [dbo].[bakgen_backuplastdt_databases] f 
                                        where f.DatabaseName = t.DatabaseName 
                                            and f.BackupType = t.BackupType 
                                            and f.ServerName = t.ServerName 
                                            and t.SqlInstanceName = f.SqlInstanceName)
             
         
        commit
 
        begin transaction
 
        delete f
            from [dbo].[bakgen_backuplastdt_fgreadonly_temp] t
                inner join [dbo].[bakgen_backuplastdt_fgreadonly] f 
                    on f.DatabaseName = t.DatabaseName 
                        and f.BackupType = t.BackupType 
                        and f.filegroup_name = t.filegroup_name
                        and f.ServerName = t.ServerName 
                        and f.SqlInstanceName = t.SqlInstanceName
                where f.LastBackupDate < t.LastBackupDate
 
 
        Insert into [dbo].[bakgen_backuplastdt_fgreadonly] (
            ServerName, 
            SqlInstanceName,
            SqlServerName,  
            DatabaseCreationDate,
            DatabaseName,   
            BackupType,
            filegroup_name,
            file_logicalname,   
            filegroup_guid, 
            file_guid,  
            LastBackupDate, 
            LastBackupReadOnlyLsn,
            is_primary,
            insertdate      
        )
        select
            t.ServerName,   
            t.SqlInstanceName,
            t.SqlServerName,
            t.DatabaseCreationDate,
            t.DatabaseName, 
            t.BackupType,
            t.filegroup_name,
            t.file_logicalname, 
            t.filegroup_guid,   
            t.file_guid,    
            t.LastBackupDate,   
            t.LastBackupReadOnlyLsn,
            t.is_primary,
            t.insertdate        
        from [dbo].[bakgen_backuplastdt_fgreadonly_temp] t              
            where not exists (
                select 1 from  [dbo].[bakgen_backuplastdt_fgreadonly] f 
                where f.DatabaseName = t.DatabaseName 
                        and f.BackupType = t.BackupType 
                        and f.filegroup_name = t.filegroup_name
                        and f.ServerName = t.ServerName 
                        and t.SqlInstanceName = f.SqlInstanceName)
 
         
        commit
    END TRY
    BEGIN CATCH
        SELECT
            @ErrorMessage = ERROR_MESSAGE(), 
            @ErrorSeverity = ERROR_SEVERITY(), 
            @ErrorState = ERROR_STATE();
 
        IF @@TRANCOUNT > 0
            ROLLBACK
         
        raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
 
    END CATCH
 
 
 
RETURN;
 
END TRY
BEGIN CATCH
    SELECT
        @ErrorMessage = ERROR_MESSAGE(), 
        @ErrorSeverity = ERROR_SEVERITY(), 
        @ErrorState = ERROR_STATE();
  
    set @InfoLog = '@ErrorState = ' + convert(nvarchar, @ErrorState) + '/@ErrorSeverity = ' + convert(nvarchar, @ErrorSeverity) + '/@ErrorMessage = ' + @ErrorMessage
    execute dbo.bakgen_p_log    
        @ModuleName = @ModuleName,
        @ProcedureName = @ProcName,
        @ExecuteMode = @Execute,
        @LogType = 'ERROR',
        @DatabaseName = null,
        @Information = @InfoLog,
        @Script = null
 
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
 
RETURN
END

其他需要的支持

如上所述，我使用了DBATools Write-DbaDbTableData函数，因此需要先安装它，然后才能运行上述存储过程。

我还分享了上述存储过程中使用的其他两个对象，但是您当然可以根据需要调整代码

日志表的创建：

--########################################################
--###Backup generator - logs
--########################################################
 
USE [<YourDatabaseName>]
GO
/*
if OBJECT_ID('[dbo].[bakgen_logs]') is not null
    drop table [dbo].[bakgen_logs]
*/
create table [dbo].[bakgen_logs] (
    id bigint identity(1,1) not null,
    LogDate datetime,
    SqlServerName sysname,
    ModuleName sysname,
    ProcedureName sysname,
    ExecuteMode char(1),
    LogType nvarchar(50),
    DatabaseName sysname null,
    Information nvarchar(max) null,
    Scripts nvarchar(max) null,
CONSTRAINT [PK_bakgen_logs] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO

创建存储过程以写入日志：

use [<YourDatabaseName>]
if OBJECT_ID('dbo.bakgen_p_log') is not null
    drop procedure dbo.bakgen_p_log 
go
 
CREATE PROCEDURE dbo.bakgen_p_log 
(
    @ModuleName sysname,
    @ProcedureName sysname,
    @ExecuteMode char(1),
    @LogType nvarchar(50),
    @DatabaseName sysname = null,
    @Information nvarchar(max) =  null,
    @Script nvarchar(max)  = null
)
 
AS
/************************************
*   dbi-services SA, Switzerland    *
*   http://www.dbi-services.com        *
*************************************
    Group/Privileges..: DBA
    Script Name......:  bakgen_p_log.sql
    Author...........:  Christophe Cosme
    Date.............:  2019-09-20
    Version..........:  SQL Server 2016 / 2017
    Description......:  write information to the log table to keep trace of the step executed
 
    Input parameters.: 
 
    Output parameter: 
                 
************************************************************************************************
    Historical
    Date        Version    Who    Whats     Comments
    ----------  -------    ---    --------  -----------------------------------------------------
    2019-10-14  1.0        CHC    Creation
************************************************************************************************/ 
BEGIN
 
BEGIN TRY
     
    --###variable to store error message
    declare @errmsg varchar(4000)
 
    if OBJECT_ID('[dbo].[bakgen_logs]') is null
    begin
        set @errmsg = 'bakgen_p_log : table not found - be sure the table exists'
        set @errmsg += '    table name = [dbo].[bakgen_logs]'
        raiserror (@errmsg,11,1);
    end    
 
    insert into [dbo].[bakgen_logs] (
        LogDate,
        SqlServerName,
        ModuleName,
        ProcedureName,
        ExecuteMode,
        LogType,
        DatabaseName,
        Information,
        Scripts
        )
    values(
        getdate(),
        convert(sysname,SERVERPROPERTY('servername')),
        @ModuleName,
        @ProcedureName,
        @ExecuteMode,
        @LogType,
        @DatabaseName,
        @Information,
        @Script
        )
 
 
RETURN;
 
END TRY
BEGIN CATCH
    declare
    @ErrorMessage  NVARCHAR(4000), 
    @ErrorSeverity INT, 
    @ErrorState    INT;
    SELECT
        @ErrorMessage = ERROR_MESSAGE(), 
        @ErrorSeverity = ERROR_SEVERITY(), 
        @ErrorState = ERROR_STATE();
  
    -- return the error inside the CATCH block
    raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
 
RETURN
END

结论

从存储过程触发PowerShell可以解决我的特殊情况，非常实用。但是找到正确的语法以使脚本通过xp_CmdShell运行并不是一件容易的事。我承认有时会花一些时间找出导致问题的原因。但是我绝对喜欢在本地SQL Server实例之外检索信息的解决方案。

来源：SQL Server –在AlwaysOn环境中收集备份信息
https://blog.dbi-services.com/sql-server-collecting-last-backup-information-in-an-alwayson-environment