deleted tables from a database

how can we find the deleted tables from a database? by whom and when? and how can we recover them again?
teodor76Asked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
In order to do all those things, you should have a professional third party tool like ApexSQL Log to achieve it:
http://www.apexsql.com/sql_tools_log.aspx
0
 
QPRCommented:
As said above you need the third party tool... but for future safety you can use a ddl trigger to forbid the dropping of tables or at least log who and when

http://riteshshah.wordpress.com/2009/03/05/ddl-trigger-in-sql-server-2005-for-create-table-alter-table-drop-table-create-procedure-alter-procedure-drop-procedure-etc/
0
 
teodor76Author Commented:
are there any other way of this except for third party tool?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QPRCommented:
If your tables have already been dropped then you won't get them back unless you can restore from a backup or a 3rd party tool.
The link that rrjegan17 gave you has the offer of a free trial. You may be able to use this to view the logs and see who did what
0
 
teodor76Author Commented:
can we install Apextool on our test server and hint it on the product server which the deleted tables is located?
0
 
QPRCommented:
Haven't used it for a while, not since I intro'd rrjegan to the site and the rewards program offered there ;-)
But from vague memory you should be able to. You may even be able to point it to a log backup rather than the live log
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> can we install Apextool on our test server and hint it on the product server which the deleted tables is located?

Its recommended to install it in the server where your SQL Server instance resides.
NOTE: This tool will try to restore as much information that is possible in Transactional log - If you have truncated your Log file, took a transactional log backup, then you can't guarantee 100% recovery.

One another workaround:
You can temporarily recover your table if you have any earlier database backup available.
Restore the earlier backup as some other database name and copy this table to your database.
You can't know who deleted the table or when it is deleted by this workaround.
0
 
Alpesh PatelAssistant ConsultantCommented:
Use third party tool to recover from log file.

Or restore old backup
0
 
lcohanDatabase AnalystCommented:
For future think about native SQL tools like DDL triggers that can log almost ALL ddl changes in your database. Also if the server was NOT restarted you may find the info under Schema Change History - native standard SQL report. Just go in SSMS, right click your DB and select the report under standard reports.

http://msdn.microsoft.com/en-us/library/ms190989.aspx
0
 
lcohanDatabase AnalystCommented:
And here's a simple but good example of how to make use of them:

http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

0
 
teodor76Author Commented:
I've requested that this question be deleted for the following reason:

we could not solve this problem
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
teodor76,

>> we could not solve this problem

You could have solved the problem if you would have gone with the third party tools approach mentioned by me..
If you can't spare for a paid third party tool, then you can't restore the deleted tables other than from Backup as mentioned above...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
_alias99,

I would recommend

Accept http:#a36472025 and http:#a36472321
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.