2015-05-17

Who Backed Up Your Database?

As cool as server-scoped DDL triggers are, it's discouraging that you can't create one to fire on a database backup event or transaction log backup event.
As far as I know, this doesn't work on SQL 2012 or earlier versions:
CREATE TRIGGER trgBackupEvent 
ON ALL SERVER 
FOR AUDIT_BACKUP_RESTORE_EVENT 
AS 
    PRINT 'Database Backup/Restore.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
Even though the above doesn't work, there are other ways to determine when a database was backed up. Not including 3rd party tools, here's a few that I've used (I'll save the best for last). If you use any other methods, please share in the comments.
  1. Query the backup history in [msdb]. It's not especially creative. And it's not automated either. No one wants to be tethered to SSMS hitting F5 on a regular basis.
  2. Add an insert trigger to [msdb].[dbo].[backupset] that sends an email when a new row is inserted. You'd need a 2nd insert trigger on [msdb].[dbo].[restorehistory] to act on any restore operations. Although simple and powerful, this option is "dirty"--it goes against the adage that you don't mess with system tables.
  3. Create a SQL Agent WMI Alert along with a SQL Agent Job that responds to the alert. Presumably, you'd set up a job step to send an email when the event occurs. Token replacement gives you a lot of information (the tsql statement, what login was used, application name, etc). Svetlana Golovko at mssqltips.com has a great article with the details. I really liked this option when I first tested it out. I came to realize it has a couple of shortcomings. It doesn't work with SQL Express, which has no SQL Agent. And the login used for the Windows Management Instrumentation service must have a SQL login with SYSADMIN role membership.
  4. Create a SQL Agent Alert for specific message id's along with a SQL Agent Job that responds to the alert. This is similar to option #3, but it doesn't use WMI, so there's no issue with the WMI service and a login with elevated permissions. It also isn't as robust as option #3. There's not as much information available via token replacement, so it's hard to determine whodunnit (although I suppose you could get the info from backup history in [msdb]). It's also not an option for SQL Server Express. Jeremy Kadlec at mssqltips.com has an article with more details.
  5. Create an event notification for AUDIT_BACKUP_RESTORE (see sample script below). This option is my favorite. It's automated, doesn't require elevated permissions for the WMI service login, and it's robust. It doesn't work on SQL 2005 or earlier, so that may be an issue for some of you. You also have to decide which database will hold a handful of objects. I briefly considered creating them in [master], but quickly decided against it. Ultimately, I created a new db for this and other purposes.
CREATE QUEUE queBackupRestoreNotification
GO

CREATE SERVICE svcBackupRestoreNotification
ON QUEUE queBackupRestoreNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

CREATE EVENT NOTIFICATION enBackupRestoreEvents
ON SERVER
WITH FAN_IN
FOR AUDIT_BACKUP_RESTORE_EVENT
TO SERVICE 'svcBackupRestoreNotification', 'current database';
GO

CREATE PROCEDURE dbo.ReceiveBackupRestoreEvent
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MsgBody XML

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION

        WAITFOR (
            RECEIVE TOP(1) 
                @MsgBody = CAST(message_body AS XML)
                FROM queBackupRestoreNotification
        ), TIMEOUT 1000  
        
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            BREAK
        END 
        ELSE
        BEGIN
            DECLARE @Login NVARCHAR(255)
            DECLARE @DB SYSNAME
            DECLARE @TsqlCmd NVARCHAR(MAX)
            
            SET @Login = @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)' )
            SET @DB = @MsgBody.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(128)' )
            SET @TsqlCmd = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(2000)')

            --Dont send email for RESTORE HEADERONLY, RESTORE FILELISTONLY, etc.
            IF @TsqlCmd LIKE '%RESTORE%DATABASE%' OR @TsqlCmd LIKE '%BACKUP%DATABASE%' OR @TsqlCmd LIKE '%BACKUP%LOG%'
            BEGIN
                DECLARE @MailBody NVARCHAR(MAX)
                DECLARE @Subject NVARCHAR(255)

                SET @Subject = @@SERVERNAME + ' -- ' + @MsgBody.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(128)' )    
                SET @MailBody = 
                    '<table border="1">' +
                    '<tr><td>Server Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ServerName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                    '<tr><td>Database Name</td><td>' + @DB + '</td></tr>' + 
                    '<tr><td>Start Time</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/StartTime)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
                    '<tr><td>Host Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/HostName)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
                    '<tr><td>NT User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/NTUserName)[1]', 'VARCHAR(128)' ) + '</td></tr>' +  
                    '<tr><td>Session Login Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                    '<tr><td>Login Name</td><td>' + @Login + '</td></tr>' + 
                    '<tr><td>DB User Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/DBUserName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                    '<tr><td>Application Name</td><td>' + @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(128)' ) + '</td></tr>' + 
                    '</table><br/>' +
                    '<p><b>Text Data:</b><br/>' + REPLACE(@TsqlCmd, CHAR(13) + CHAR(10), '<br/>') +'</p><br/>'

                EXEC msdb.dbo.sp_send_dbmail 
                    @recipients = 'You@YourDomain.com', 
                    @subject = @Subject,
                    @body = @MailBody,
                    @body_format = 'HTML'
            END

            COMMIT TRANSACTION
        END
    END
END
GO 

ALTER QUEUE dbo.queBackupRestoreNotification 
WITH 
STATUS = ON, 
ACTIVATION ( 
    PROCEDURE_NAME = dbo.ReceiveBackupRestoreEvent, 
    STATUS = ON, 
    --STATUS = OFF, 
    MAX_QUEUE_READERS = 1, 
    EXECUTE AS OWNER) 
GO

*** Update 2015-05-18 ***
There's an important detail I neglected to mention for option #5. After a test db backup, the email alert may never get sent and you will see a similar message in the error log:
The activated proc '[dbo].[ReceiveBackupRestoreEvent]' running on queue 'YourDB.dbo.queBackupRestoreNotification' output the following: 'The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.'

Setting the database to trustworthy "fixed" this issue for me.

ALTER DATABASE YourDB
SET TRUSTWORTHY ON

The db I used and all the contents within it were created by me. I didn't hesitate to make the change. Nonetheless, making a db trustworthy carries some ramifications you may not be comfortable with. Proceed as you see fit.

SHARE