Database Deleted?

Question

Database Deleted?

2Tall

SSChampion

Points: 14644
More actions
March 6, 2010 at 1:17 am

#212136

Hi. A vender requested I run a script against their database hosed on SQL Express. I connected to the instance expanded databases and NO database?
I checked the logs but can not find a record for DROP or DELETE?
I have re-created and restored from backup, however I MUST find out who deleted it in the first instance.
Any ideas?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

For one that has already been dropped, you will not be able to determine that unless you had auditing enabled or a DDL trigger that logs such actions.

Recommendation: Create a server side ddl trigger that will log such actions to an alternate database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks Jason, stumped then in this instance. I will look into your suggestions.

Is there a stored proc I can run to obtain login information against a database login account (created user, created by etc...)

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

This article might be useful for you.

http://www.mssqltips.com/tip.asp?tip=1571

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

2Tall SSChampion Points: 14644 More actions · Answer 4

Thanks for the link.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

2Tall SSChampion Points: 14644 More actions · Answer 6

It appears you can log events on the database (DROP_TABLE) but not log if the database itself is dropped.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

If the default trace was running when the object was deleted, you can look in the trace file and find when and who dropped the database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

2Tall SSChampion Points: 14644 More actions · Answer 8

Thanks Jeffrey, I will take a look into that.

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 9

If I'm correct, the default trace isn't running by default in SQLExpress :sick:

You should enable it using sp_configure 'default trace enabled', 1

Same goes for the DAC.

If you want a DAC, you should add startup parameter 7806 for your sqlexpress instance.

Put it in your post install scripts !!

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

2Tall SSChampion Points: 14644 More actions · Answer 10

Thanks. I do not believe I have DAC enabled for any instance of SQL at this time.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper