Setting up an alert to monitor where/when your databases are backed up

Question

I was reading Who’s Backing Up That Database? by Kendra little, specifically the section, "IF THE BACKUP IS Succeeding, YOU CAN TRY ANOTHER TRICK".

I have been looking into monitoring my databases to send an email when the databases are backed up to another location outside of my backup path. So far what I have come up with has been is to set up a job that would fire off every 5 minutes which contained the following script:

BEGIN
IF (SELECT COUNT (bmf.physical_device_name)
FROM [msdb].[dbo].[backupset] bs inner join [msdb].[dbo].[backupmediafamily] bmf
on bs.media_set_id = bmf.media_set_id
where bs.[database_name] = 'logbackuptest'
and bmf.physical_device_name not like 'D:\backups\%') > 0

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'DBA Profile',  
    @recipients = 'IT@ITCORP.com',  
    @body = 'The database was backed up to a different location',  
    @subject = 'A database was backed up to a different location' ; 
END

Does anyone else have a good blog post or any other ideas? I'm not in a situation where backups are happening to non-default locations without my knowledge; the question is purely academic.

score 3 · Accepted Answer · edited Dec 23 '16 at 16:41

You can use a BackupRestore Event Notification to monitor backup events and this solution even works on Sql Express (where the Sql Agent doesn't exist). This link shows you how to set up SQL Mail for SQL Server Express.

I created a database called Test.

I modified this example from Jonathan Kehayias, which was tracking autogrowth, to instead audit the backup/restore event.

Here is my example that was tweaked from Jonathan's:

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

I then tweaked his example stored procedure that consumes the event.

Make sure you change this line in the stored procedure to be your email address

@recipients = 'YourEmailAddress'

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

Then, I ran a simple backup

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

After a minute or so, I received the email with the event data in it. You can modify the event consuming stored procedure to capture whatever you need and send that information along in the email.

score 0 · Answer 2 · answered Nov 01 '16 at 16:55

Turn off SQL Server trace flag -3226 if you have it applied. I usually recommend turning 3226 on so all successful backups are not written to the SQL Server log. In this case however, that is the behaviour we want
Schedule the following PowerShell via SQL Agent or Windows Scheduler:

Get-SqlErrorLog -ServerInstance 'YourInstanceName' | ? { ($.Text -match 'Database backed up' -and $.Text -notmatch 'D:\\')}

The double \\ is important above for this to work
Pipe the PoSh output to email interested parties

Setting up an alert to monitor where/when your databases are backed up

2 Answers2

Linked