• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

How to retrieve deleted table?

I delete table1 from database db1 using DELETE command.

It is said that delete command is logged. I would like to bring back the deleted table.

How to achieve that? Please do explain with example/sample.
0
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
  • 4
  • 3
1 Solution
 
momi_sabagCommented:
unless you have a backup of the database from before the backup, you won't be able to restore the data

the only way to do it is to buy a tool that can parse the log files (redgate has one) and try to generate the reverse statements for the delete you executed
0
 
Easwaran ParamasivamAuthor Commented:
Ok. Assume I've backup.

In this case whether restore the entire database is the only option to bring back the table or some other option exists? If so, please do explain in detail.
0
 
Jared_SCommented:
1) Restore your database under a new name.

2) Move the table from your old database to your new database.
(You can do this easily by running a query similar to "select * into OldDatabase.dbo.OldtableName from NewRestoredDatabase.dbo.RestoredTableName")

3) delete your restored database
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Easwaran ParamasivamAuthor Commented:
@Jared_S Thanks for your comments.

I know above process by restoring the database.

As restore and delete old database is common one. Is there any other alternate option such as reading the log file and restore only the deleted table?

Please do not suggest commercial tool.

Please do provide your comments.
0
 
Jared_SCommented:
Was your database recovery mode set to full?
0
 
Easwaran ParamasivamAuthor Commented:
Yes.
0
 
Jared_SCommented:
You'll have to restore your full backup, and then restore your transaction logs sequentially up to the point prior to the delete command by using STOPAT.

Read these carefully and take another backup before doing your restore.

http://msdn.microsoft.com/en-us/library/ms189596.aspx

http://msdn.microsoft.com/en-us/library/ms186858.aspx

There is a reason that this process can be commercialized, so I wish you the best.

As an alternative - RedGate offers a 14 day full trial on a lot of their products.
I don't know if they would limit the number of rows that could be restored( anyone?),
but it might be worth checking out.
0
 
Easwaran ParamasivamAuthor Commented:
Thanks.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now