Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SreenivasBora
Posting Yak Master
164 Posts |
 Posted - 2007-02-21 : 17:09:37
|
| Guys,How to trace/find out some one has dropped a database from my QA environment? Unfortunately we havent enabled the trace on this server.We havent find any useful information from SQL Server logs also.Can any one reply me how to find the details of who dropped the DB, when? is there any query/SP/command/tools avaialble?RegardsBSRWith RegardsBSR |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-21 : 19:22:10
|
| you might be able to get this information by using lumigent Log Explorer or red-gate SQL Log rescue. These are the only tools that I know of that will allow you to look at the SQL transaction logs.-ec |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-21 : 19:39:02
|
I don't think that DROP DATABASE is logged, so I don't think that information will be stored anywhere such as in the transaction log. This generates an error:BEGIN TRANDROP DATABASE Test1ROLLBACK TRANquote:
Msg 574, Level 16, State 0, Line 2DROP DATABASE statement cannot be used inside a user transaction.
Tara Kizer |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-21 : 20:41:57
|
| that is really good point. in fact, you wouldn't even have LOG to point log explorer at after you drop the database.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 21:18:44
|
"How to trace/find out some one has dropped a database from my QA environment?"Probably no help, but you could restore MASTER from backups (to a TEMP database) to work out WHEN the database name disappeared from the sysdatabases table.But if you already know WHEN then that will be no help. If knowing WHEN helps you to know WHO  then it may be of some help!But I doubt your backups of MASTER are of sufficient granularity for this to be useful, I know that mine aren't ... I wonder whether hourly DIFFs of MASTER would be a useful contingency ...Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 21:22:47
|
| "you wouldn't even have LOG to point log explorer at after you drop the database."The LOG in MASTER database? There has to be a delete from the sysdatabases table? But its in SIMPLE mode so I don't suppose there is any trail for a log reader to audit.Kristen |
|
|
|
|
|
|
How to Trace MS-SQL ....