Solved

deleted tables from a database

Posted on 2011-09-02
14
197 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

777 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