Solved

deleted tables from a database

Posted on 2011-09-02
14
196 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
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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 39

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 39

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now