设置警报以监控备份数据库的位置/时间

您可以使用BackupRestore Event Notification 来监视备份事件，此解决方案甚至适用于 Sql Express（Sql 代理不存在）。此链接向您展示了如何为 SQL Server Express 设置 SQL Mail。

我创建了一个名为 Test 的数据库。

我从跟踪自动增长的 Jonathan Kehayias 修改了这个示例，改为审计备份/恢复事件。

这是我从乔纳森的例子中调整而来的例子：

-- Using msdb prevents the need for certificate signing the 
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO

-- Drop the notification if it exists
IF EXISTS (
        SELECT *
        FROM sys.server_event_notifications
        WHERE NAME = N'CaptureDatabaseBackupEvent'
        )
BEGIN
    DROP EVENT NOTIFICATION CaptureDatabaseBackupEvent ON SERVER;
END

-- Drop the route if it exists
IF EXISTS (
        SELECT *
        FROM sys.routes
        WHERE NAME = N'DatabaseBackupEventRoute'
        )
BEGIN
    DROP ROUTE DatabaseBackupEventRoute;
END

-- Drop the service if it exists
IF EXISTS (
        SELECT *
        FROM sys.services
        WHERE NAME = N'DatabaseBackupEventService'
        )
BEGIN
    DROP SERVICE DatabaseBackupEventService;
END

-- Drop the queue if it exists
IF EXISTS (
        SELECT *
        FROM sys.service_queues
        WHERE NAME = N'DatabaseBackupEventQueue'
        )
BEGIN
    DROP QUEUE DatabaseBackupEventQueue;
END

--  Create a service broker queue to hold the events
CREATE QUEUE [DatabaseBackupEventQueue]
    WITH STATUS = ON;
GO

--  Create a service broker service receive the events
CREATE SERVICE [DatabaseBackupEventService] ON QUEUE [DatabaseBackupEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [DatabaseBackupEventRoute]
    WITH SERVICE_NAME = 'DatabaseBackupEventService'
        ,ADDRESS = 'LOCAL';
GO

-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureDatabaseBackupEvent] ON SERVER
    WITH FAN_IN
FOR AUDIT_BACKUP_RESTORE_EVENT TO SERVICE 'DatabaseBackupEventService'
    ,'current database';
GO

--  Alter the queue to use the activation procedure
ALTER QUEUE [DatabaseBackupEventQueue]
   WITH STATUS=ON, 
      ACTIVATION 
         (STATUS=ON,
          PROCEDURE_NAME = [ProcessDatabaseBackupEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

然后我调整了他使用事件的示例存储过程。

确保将存储过程中的这一行更改为您的电子邮件地址

@recipients = '你的邮箱地址'

DROP PROCEDURE [dbo].[ProcessDatabaseBackupEvents];
GO

CREATE PROCEDURE [dbo].[ProcessDatabaseBackupEvents]
    WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);

WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;

    -- Receive the next available message FROM the queue
    WAITFOR (
            RECEIVE TOP (1) -- just handle one message at a time
            @message_body = CAST(message_body AS XML) FROM dbo.DatabaseBackupEventQueue
            )
        ,TIMEOUT 1000;-- if queue empty for 1 sec, give UPDATE AND GO away

    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION;

        BREAK;
    END

    DECLARE @EventType VARCHAR(128);
    DECLARE @ServerName VARCHAR(128);
    DECLARE @PostTime VARCHAR(128);
    DECLARE @DatabaseName VARCHAR(128);
    DECLARE @TextData VARCHAR(max);
    DECLARE @GrowthPages INT;

    SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)')
        ,@TextData = @message_body.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)')
        ,@ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)')
        ,@PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS VARCHAR)
        ,@DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)');

    -- Generate formatted email message
    SELECT @email_message = 'The following DatabaseBackup event                                      occurred:' + CHAR(10) + CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + @TextData

    -- Send email using Database Mail
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,
        -- your defined email profile 
        @recipients = 'YourEmailAddress'
        ,-- your email
        @subject = 'DatabaseBackup Event Notification'
        ,@body = @email_message;

    --  Commit the transaction.  At any point before this, we could roll 
    --  back. The received message would be back on the queue AND the 
    --  response wouldn't be sent.
    COMMIT TRANSACTION;
END
GO

然后，我运行了一个简单的备份

BACKUP DATABASE [Test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT
    ,INIT
    ,NAME = N'Test-Full Database Backup'
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10
GO

大约一分钟后，我收到了包含事件数据的电子邮件。您可以修改使用事件的存储过程以捕获您需要的任何信息并在电子邮件中发送该信息。

• 如果应用了 SQL Server跟踪标志-3226，请将其关闭。我通常建议打开 3226，这样所有成功的备份都不会写入 SQL Server 日志。然而，在这种情况下，这就是我们想要的行为
• 通过 SQL Agent 或 Windows Scheduler 安排以下 PowerShell：

获取 SqlErrorLog -ServerInstance 'YourInstanceName' | ？{ ($ .Text -match '数据库备份' -and $ .Text -notmatch 'D:\\')}

• 双 \\ 对上面的工作很重要
• 将 PoSh 输出通过管道发送给感兴趣的各方