[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

deleted tables from a database

Posted on 2011-09-02
14
Medium Priority
?
207 Views
Last Modified: 2012-05-12
how can we find the deleted tables from a database? by whom and when? and how can we recover them again?
0
Comment
Question by:teodor76
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1000 total points
ID: 36472025
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
 
LVL 29

Expert Comment

by:QPR
ID: 36472088
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
 

Author Comment

by:teodor76
ID: 36472122
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.

 
LVL 29

Expert Comment

by:QPR
ID: 36472243
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
 

Author Comment

by:teodor76
ID: 36472255
can we install Apextool on our test server and hint it on the product server which the deleted tables is located?
0
 
LVL 29

Expert Comment

by:QPR
ID: 36472271
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 36472321
>> 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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36472806
Use third party tool to recover from log file.

Or restore old backup
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36474904
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36474922
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
 

Author Comment

by:teodor76
ID: 36573390
I've requested that this question be deleted for the following reason:

we could not solve this problem
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 36573391
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 36578251
_alias99,

I would recommend

Accept http:#a36472025 and http:#a36472321
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question