Is there any way I can find out who deleted the database?
-
first thing you need to do is save the security log on the server
right click on it and save as
then look in it. i think the event id is 540 or 538 and it should show the IP and/or hostname. but it all depends on your domain audit policy as well as the local server audit policy
-
Jeff Moden (3/26/2008)
Todd Engen (3/26/2008)
Jeff,Try changing "DROP" to "DELETE", or set a WHERE clause with EventClass = 47 for Object : Deleted events.
Opening the traces in Profiler will resolve the EventClass to readable names too.
Perfect... I'll give that a try. Thanks, Tod.
EventClass is the key. TextData is largely null in these trace files....(in mine at least).
also - note the sys.Trace_events view has the list of all the "friendly names" of all trace events (not just the default trace ones).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thanks Matt sys.trace_events is much easier 🙂
-
Adam Haines (3/26/2008)
Thanks Matt sys.trace_events is much easier 🙂What do you know - I actually gave the MS developers credit that they wouldn't just leave us hanging like that. So I looked in the only place I know (the DMV's), and voila... Once I had the name - I confirmed what it really does from BOL.
Boy - learning all sorts of good stuff today...:D
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
That was very usefull info. Thanks eveyone
"Keep Trying"
-
for those who don't want to write query, go to management studio, right click on Server, Go to Reports > Standard Reports > "Schema Change History"
This report picks data from the default trace file.
I hope this is new to few of you 😛
-
Hi,
As a SQL dba you have to take care of security and database backups and you had done neither of them and you are suffering right now.
[font="Verdana"]Thanks
Chandra Mohan[/font] -
I've used a product called 'getdataback' in the past, it even recovers data when a drive has been formatted. Might be worth taking a look at.
-
Assuming this is SQL2k5, just go to the default log folder and open the latest trace file in Profiler. It will show you the logs.
-
Great tips!
-
Do you have any tips for SQL 2000?
-
I assume someone dropped the database using Management Studio.
It has a default do delete all backup info as well.
AFAIK, you still need to delete the backup files manualy.
So have a look at your backup folder, you may still find a bak file !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Balmukund Lakhani (3/27/2008)
for those who don't want to write query, go to management studio, right click on Server, Go to Reports > Standard Reports > "Schema Change History"This report picks data from the default trace file.
I hope this is new to few of you 😛
Thanks for reminding us, we tend to forget to use this lovely facility :hehe:
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ALZDBA (3/27/2008)
I assume someone dropped the database using Management Studio.It has a default do delete all backup info as well.
AFAIK, you still need to delete the backup files manualy.
So have a look at your backup folder, you may still find a bak file !
I usually uncheck that option so I can have a history of the database backups in case I need to look for them on tape someday ... (except for temporary/junk databases).
-
Thanks a ton, guys!
In did find the culprit... someone did it through Management Studio.. it was a mistake of course so the developers (who the DB belonged to have decided to ignore this.. I know, amazing people!).. no luck recovering any backups either 🙁
And.. just for your info "chandramohann".. I'm no DBA.. and I definitely wasn't even aware of the development on this particular DB or even server for that matter.. I was just trying to help some people at work..
In the process, I obviously got to learn lots of new stuff from all these wonderful people here..
Its lovely that people post replies when they have 'solutions' or 'queries' and not just give 'advice based on assumptions' or just 'pass statements' for the heck of it.
Guys, really.. you people are my rescue boats.. each time!
Keep up the great work!
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply