SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Check deleted table		  Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

thotrang
Starting Member

13 Posts
Posted - 11/16/2006 :  08:04:35  Show Profile
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

Sweden
29156 Posts
Posted - 11/16/2006 :  08:12:43  Show Profile  Visit SwePeso's Homepage
Who did a RESTORE might be the proper question?
Are you missing some other data too?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 11/16/2006 :  08:15:34  Show Profile
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7007 Posts
Posted - 11/16/2006 :  08:43:37  Show Profile
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
Go to Top of Page

thotrang
Starting Member

13 Posts
Posted - 11/16/2006 :  08:52:49  Show Profile
I mean that is there any funtion in the SQL server 2000 can check this problem. May be view all transaction .
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 11/16/2006 :  09:02:24  Show Profile  Visit SwePeso's Homepage
Good luck!
This might help, if the backup has been masde since deletion.

select * from ::fn_dblog(default, default)



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 11/16/2006 :  13:18:23  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000