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 |
|
thotrang
Starting Member
13 Posts |
 Posted - 2006-11-16 : 08:04:35
|
| After one night one table in my database disapear. I want to check where and who (user) deleted this table. How can I do this?Could any body help me ?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-16 : 08:12:43
|
| Who did a RESTORE might be the proper question?Are you missing some other data too?Peter LarssonHelsingborg, Sweden |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-16 : 08:15:34
|
| Also, search syscomments for procs or functions that may drop that table. I once had a long release script where I forgot the go between a alter proc and a drop table. The drop got compiled into my proc (in error) and it took me forever to figure out why that table kept getting dropped every night ...Jay White |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-16 : 08:43:37
|
quote:
Originally posted by thotrang
After one night one table in my database disapear. I want to check where and who (user) deleted this table. How can I do this?Could any body help me ?Thanks
Start by lookimg to see who has access to drop the table.Then ask them if they did it.CODO ERGO SUM |
|
|
|
thotrang
Starting Member
13 Posts |
Posted - 2006-11-16 : 08:52:49
|
| I mean that is there any funtion in the SQL server 2000 can check this problem. May be view all transaction . |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-16 : 09:02:24
|
| Good luck!This might help, if the backup has been masde since deletion.select * from ::fn_dblog(default, default)Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-16 : 13:18:23
|
| If your database uses FULL Recovery model it would be possible to selective restore the database (e.g. to another, temporary, database) and see the point at which the table was dropped, but it won't tell you who did it - but other activity in the database at the time the table was dropped might help identify the culprit.You could also try a log reader - from RedGate or Lumiscent(sp?)Kristen |
|
|
|
|
|
|
Check deleted table