database got deleted

Question

Post reply

database got deleted

Krasavita

SSC-Insane

Points: 23138
More actions
June 9, 2011 at 11:57 am

#241057

Hello, someone deleted db,can I find out who did it and when?

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Check the default trace.

SELECT * FROM ::fn_trace_getinfo(default)

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

This what I got:

traceidpropertyvalue

112

12C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_255.trc

1320

14NULL

151

211

22NULL

23NULL

24NULL

251

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 3

Note that the default trace is limited in size, so it might not be there.

This can be hard to track down if a lot of activity has happened on the server or a lot of time has passed.

If the physical files are still there (depending on version), you can attach back the db.

Default Trace:http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Krasavita (6/9/2011)
This what I got:
traceidpropertyvalue
112
12C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_255.trc
1320
14NULL
151
211
22NULL
23NULL
24NULL
251

Now try this

Select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_255.trc')

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

An insufficient number of arguments were supplied for the procedure or function fn_trace_gettable.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Add "default" as a second parameter:

Select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_255.trc', default)

- 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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

GSquared (6/9/2011)
Add "default" as a second parameter:
Select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_255.trc', default)

Thanks for covering Gus.

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

Krasavita (6/9/2011)
An insufficient number of arguments were supplied for the procedure or function fn_trace_gettable.

Other way to find the default trace,old fashioned way RDP to the server do a search with "*.trc" 😀