Phantom of the Nopera
Let’s say you have a server. Let’s go one step further and say SQL Server is running on it. Crazy, I know.
But hey, we specialize in crazy, here.
If you want to track CREATE, ALTER, or DROP DATABASE commands, that’s easy enough with DDL triggers.
But guess what? None of those fire when you RESTORE a database. Weird, right?
Other triggers?
Astute SQL-ers may attempt to add a trigger to the restorehistory table over in msdb. It’s in the dbo schema, which might make you hopeful. We all know triggers in that pesky sys schema don’t do a darn thing.
You guessed it, restores get tracked there. So there’s, like, something inside SQL telling it when a restore happens.
Guess what, though? A trigger on that table won’t fire. Not FOR INSERT, not AFTER INSERT, and not nothin’ in between.
Extended Events
There’s even an event! The backup_restore_progress_trace tracks this stuff. There’s even some cool information in there.
Skip a few…
Exciting stuff, right? But… that’s a lot of extra info. And, wait… How does this help us? How do we take that and fire a notification?
I’m here to assure you, dear readers, that is is possible.
The proof is in this THIRTY-FOUR PAGE PDF.
Nothing against pages, the number 34, PDFs, or Jason Strate, of course. But if you need a 34 page manual for something, the barrier to entry is, well, 34 pages high. I’m bailing on that with a quickness you only see on Maury when someone is not the father.
Polling
That leaves you with rather ugly options. You can write a query that checks in with sys.databases (or even that restore history DMV) to look for new stuff being added. But that means you’re likely firing up an Agent job, or adding application code to do that, and then start doing ~something~ with the new database.
That’s pretty lousy. If you agree (and of course you do, that’s why you’re here!), there’s a Connect Item filed by Aaron Bertrand, that was unfortunately closed almost as soon as it was opened, back in 2010.
Maybe if enough of you kind folks vote on it, it will be reopened.
Thanks for reading!
Brent says: if you read yesterday’s post about the new sp_AllNightLog, you can probably connect the dots. We needed to be able to track when new databases are created, and that was fairly easy, but tracking restores into new database names was much trickier.
17 Comments. Leave new
How about an event notification for AUDIT_BACKUP_RESTORE_EVENT?
So you’d have to… Set up an audit, set up service broker, create an event notification. Not sure that’s less complicated, but I’ve never tried it.
The design pattern I use involves creating a QUEUE, a SERVICE, an EVENT NOTIFICATION, and an activation PROCEDURE to process records in the QUEUE. It’s asynchronous…so there’s that. A little complicated? I guess it depends on who you ask.
On another note, I find the AUDIT_BACKUP_RESTORE_EVENT kind of frustrating. I wish there were separate events: one for backup, one for restore. But I digress…
I think I’m just gonna stick to checking in on sys.databases every so often 🙂
I once implemented all that notification stuff you’re talking about. ( http://michaeljswart.com/2016/09/build-your-own-tools/ ) It wasn’t easy and building an automatic/robust/safe deployment for others is impossible.
(Upvoted)
Thanks! And yeah, the idea of doing that in line with the Faux PaaS project, where servers get killed off, and new servers get brought up automatically, yeesh. Setting up Service Broker stuff is hard enough when it’s NOT dynamic SQL. Heh.
> Guess what, though? A trigger on that table won’t fire. Not FOR INSERT, not AFTER INSERT, and not nothin’ in between.
This remains the real mystery to me (and apparently lots of other people: http://lmgtfy.com/?q=triggers+fire+in+msdb)
it seems that triggers on that restorehistory fire for ad-hoc INSERTs, but do not fire INSERTs done by the system as part of the restore routine. any inkling as to why?
things i can think of would be:
1.) system disables triggers on the table and then re-enables after INSERT
2.) system partition switches the table into a trigger-less table, does INSERT, switches back
3.) ????
and about those things, i think:
1.) does not appear to be the case – i just tested checking modify_date in sys.objects after a RESTORE for a TRIGGER i made (which does indeed fire on my adhoc INSERT but does not fire on RESTORE) and the date doesn’t change.
2.) sounds farfetched – but maybe not? it could switch into a system table or something.
3.) ????
You know they just use DBCC WRITEPAGE to insert the record.
Default trace?
That’s how I do it; eventclass = 115 and eventsubclass = 2 (but filter out as the text also contains restore log, headeronly etc). Simple one-liner, no XML to decode, get the login id, hostname and the complete restore command. (I know profiler trace is supposed to go away but I’m betting on it sticking around longer that what Microsoft is threatening).
As long as you have default enabled as a standard practice and can automate and collect the relevant stuff before the files roll over. And I just quickly query the trace files using sys.fn_trace_gettable. I’ve always used this to figure out who/why some database popped up where it wasn’t supposed to and I usually see at least a few days to a week of data in the 4 trace files it maintains.
I’m not sure that finding the path and doing the reverse/charindex/reverse/concatenate stuff, then doing all the filtering necessary is easier than just looking at sys.databases to see if something is there that isn’t in our worker table.
What about watching for error log entries? There’s a nice entry that shows both the name and the file from which it was restored, in case someone restores from one file name to another db name, or you need to know from which backup file that db was restored. Maybe about the same amount of work as just watching sys.databases for changes – you’re polling one way or another.
Or here’s another thought – since you’re doing this for your AllNightLog… what if you did something like what you can do with regular log shipping, where you set the db in standby mode to allow readers? Not because you want to let people read, but because that could be an ALTER DATABASE command that might fire the DDL server trigger that you want?
The error log thing puts us in the same position as watching Extended Events or using the default Trace. Right now, restoring to a different database name isn’t a configurable option. You gotta dance with who you brung. Maybe down the line, but way too complicated for v1.
The trouble with the ALTER DATABASE idea is that would only show on the DR server. We don’t want Primary or DR to have to talk to each other or rely on each other in any way. The only thing they have in common is a file share. It would only get restored on DR, and then altered, after we’d already backed it up on the primary. Bit of a catch 22. If we’re already backing it up and restoring it, we don’t need the DDL trigger.
Thanks!
Yeah, makes sense. I got stuck in the “why can’t I use a DDL trigger for a restore database?” thought path, along with log shipping things.
But I’m curious, it feels like you’re attempting to reinvent log shipping, why not try leveraging more of the log shipping features as is, with just the automation layer on top that watches for new db and just runs the built-in log shipping procedures to configure it? Does that not scale the way you want (creates too many jobs)?
That’s exactly what we’re doing. For more information on why, read Brent’s other related posts.
Upvoted – lets make connect a full citizen
Awesome, thanks!
I created a custom Alert, monitoring for Error 4356. Then I have a SQL Agent job that is triggered when this alert fires.