February 7, 2011 at 10:16 am
I was looking through the SQL Server Logs and attempting to get a sense of whether I could expect a db restore to get logged? I certainly get my backups logged but I began to wonder if a dba screwed something up and did a restore, would it be reflected in the log? If a database was dropped or taken off-line should I expect that to be recorded? What would the 'source' of the logging be recorded as because I've used a source = 'restore' filter and get nothing and source = 'backup' has only shown me backup althought I am reasonably certain we've done restores as part of our development refresh.
Any clues on how the logging is delineated and controlled would be very helpful.
Thanks,
Tim Munn
University of Maryland, School of Pharmacy
February 7, 2011 at 10:57 am
Have you checked the restore tables in msdb on that server?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 11:40 am
Nope. Hadn't looked there. May need to look at some backups as the activities go from February, 20011 back to May 2010 with nothing in between.
Thanks for pointing me in the right direction...
February 7, 2011 at 1:47 pm
Also, check the default trace for the server. Are you familiar with querying traces? It should include any drop/create statements for databases, I think.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 7, 2011 at 7:48 pm
Nope. Can't say I've ever explored trace files. If you'd be so kind, I am glad to learn...
Thanks again,
Tim
February 8, 2011 at 4:53 am
And as a further followup, any idea what might cause msdb.dbo.restorehistory to be cleared out periodically and how often would this rollover occur? My system db maintenance plan just checks db integrity and does a backup of the dbs.
February 8, 2011 at 7:11 am
A maintenance plan can have a "clean up history" step. That's the most usual cause of data in that table going away. Check the scheduled jobs in SQL Agent, see if there's anything with a step like that.
On traces, I'd start by looking into the functions fn_trace_getinfo and fn_trace_gettable. Search online for "sql server default trace" for data on what it contains. MSDN and BOL both have data on the functions and the default trace. That's really the best place to start.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply