Sql-server – Retrieve logon trigger from backup

sql serversql-server-2012trigger

A logon trigger was added and dropped from our production database. I have full backup of master after the trigger was added, and before it was dropped. I am trying to recover from the backup but I cannot see it under the server_triggers of that backup. Is there a way to recover logon trigger from a DB backup?

The backup is from 11PM yesterday night. Trigger was added yesterday morning and dropped today morning.

Best Answer

Restoring master is a bit of a pain.

I assume you are just trying to recover the definition?

I just tried creating a logon trigger, backing up master and opening the *.bak file in notepad and searching for "CREATE TRIGGER" - it was plainly visible in the file.

Alternatively you could restore it as a regular user database (assume master2 below) and see if the following finds it (will need to connect via the DAC)

USE master2

SELECT *,
       CAST(imageval AS NVARCHAR(MAX)),
       CAST(imageval AS VARCHAR(MAX))
FROM   sys.sysobjvalues
WHERE  CAST(imageval AS NVARCHAR(MAX)) LIKE N'%TRIGGER%'
        OR CAST(imageval AS VARCHAR(MAX)) LIKE '%TRIGGER%'

The above worked for me. YMMV and make sure you sanity check any results (the first method definitely won't work if the definition is split over multiple pages and may find ghosted versions).

If the trigger object is encrypted, I imagine it would be visible in sys.sysobjvalues using the DAC, though certainly my LIKE clauses wouldn't find it. You would need to look in sys.sysschobjs and get the objid from the name. Hopefully it isn't encrypted or it would need a good read of this article: The Internals of WITH ENCRYPTION

Related Question