Need to retrieve logon trigger from DB backup
-
A logon trigger was added and dropped from our production DB. I have full DB backup after the trigger was added and before it was dropped. I am trying to recover from the DB 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?
-
Logon triggers will be in a backup of master, not of user databases (they're server objects)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail.
Sorry, I forgot to mention. I restored the master backup and I am querying the DB but I cannot find it. I am running the below query
select * from [masterbackup].sys.server_triggers
No rows returned
The trigger itself was added yesterday morning and dropped today morning after a blocking issue. I am working with a full backup from yesterday 11PM
am I doing something wrong?
-
You'll probably need to read the underlying system tables from the master DB restore, rather than the view which (since it isn't in master at all) is probably referencing the real master DB on that server.
Can't you get the trigger out of your source control system? Will be far less work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
also, check your schema changes history. if not too much time has occurred, the definition might still be in the default trace.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Well, that's the problem. This didn't come from our source control. It was a rogue trigger added by our hosting provider and dropped when users started getting kicked out. I pull down nightly backups. I realize this is a needle in the haystack; is there anyway I get this rogue code?
-
I found this interesting, so i duplicated your issue.
I created a couple of server triggers, backed up master and restored it as a new name [masterbackup]
even though i've connecting via a Dedicated Admin Connection, after deleting those same server triggers in master, i cannot find them in the masterbackup database.
it seems select * from masterbackup.sys.server_triggers shortcuts to the masterdatabase regardless.
is this a situation where you need a backup and restore of the resource database, and not master?
even though i'm in DAC, when i try to modify the normal underlying query for sys.server_triggers, i get errors.
Msg 195, Level 15, State 10, Line 14
'sysconv' is not a recognized built-in function name.
SELECT o.name,
object_id = o.id,
parent_class = o.pclass,
parent_class_desc = pc.name,
parent_id = o.pid,
type = o.type,
type_desc = n.name,
create_date = o.created,
modify_date = o.modified,
is_ms_shipped = sysconv(bit, o.status & 1),-- OBJALL_MSSHIPPED
is_disabled = sysconv(bit, o.status & 256) -- OBJTRG_DISABLED
FROM masterbackup.sys.sysschobjs o
LEFT JOIN masterbackup.sys.syspalnames n ON n.class = 'OBTY' AND n.value = o.type
LEFT JOIN masterbackup.sys.syspalvalues pc ON pc.class = 'UNCL' AND pc.value = o.pclass
WHERE o.type IN ('TA','TR') AND o.nsclass = 20 AND o.pclass = 100-- x_eonc_TrgOnServer:x_eunc_Server
AND has_access('TR', o.id, o.pid, o.nsclass) = 1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Don't try to modify a system object (sys.server_triggers), just query the tables directly (and you don't need that conversion function, so omit the lines that use it). sysschobjs is probably the place to start, though whether the definition is in there or not is another matter. Might also want to see what tables sys.sql_modules looks at.
And no, you don't need a restore of the resource DB. The server trigger will be in master's system tables. ResourceDB just has the definition of the built-in system views.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (6/8/2016)
Don't try to modify a system object (sys.server_triggers), just query the tables directly (and you don't need that conversion function, so omit the lines that use it). sysschobjs is probably the place to start, though whether the definition is in there or not is another matter. Might also want to see what tables sys.sql_modules looks at.this was a fun rabbit hole to run down, thank you Gail. i was not modifying the view, per se, but using the entire query from the view, which was the issue.
excluding some of the internal functions like has_access and sysconv was the key.
i was able to see my two sample triggers like this:
SELECT o.name,
object_id = o.id,
definition = object_definition(o.id),
parent_class = o.pclass,
parent_class_desc = pc.name,
parent_id = o.pid,
type = o.type,
type_desc = n.name,
create_date = o.created,
modify_date = o.modified
FROM masterbackup.sys.sysschobjs o
LEFT JOIN masterbackup.sys.syssingleobjrefs x ON x.depid = o.id
LEFT JOIN masterbackup.sys.syspalnames n ON n.class = 'OBTY' AND n.value = o.type
LEFT JOIN masterbackup.sys.syspalvalues pc ON pc.class = 'UNCL' AND pc.value = o.pclass
WHERE o.type IN ('TA','TR') AND o.nsclass = 20 AND o.pclass = 100-- x_eonc_TrgOnServer:x_eunc_Server
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell
This is very interesting. I am still getting no results returned. Executing this query via DAC
I downloaded the resource dbs, I will try again after I replace the resource db and report back
-
NO!
Don't touch the resource DB. You don't need to, there are no user-defined objects in there, and you could easily toast the SQL instance. Leave the resource DB alone.
If Lowell's query returns no server triggers, then it's most likely that there were no server triggers at the time the backup was taken. Either the backup's not from when you thought it was, or the trigger was either created later or dropped earlier
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
There must be some truth in this:
If Lowell's query returns no server triggers, then it's most likely that there were no server triggers at the time the backup was taken. Either the backup's not from when you thought it was, or the trigger was either created later or dropped earlier
How long and consistent is your chain of master db backups?
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply