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
DROP DATABASE commands, that’s easy enough with DDL triggers.
But guess what? None of those fire when you
RESTORE a database. Weird, right?
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.
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.
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.