Solved

deleted tables from a database

Posted on 2011-09-02
14
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 250 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

762 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