Recover deleted table from the database

Question

Currently I deleted one table from the database. But now I want some of the information from that table.

Please suggest how to restore or get back the table.

I am using SQL Server 2008 R2.

DBMS? Oracle, SQL-Server, DB2? Do you have backups of the database? — ypercubeᵀᴹ, Feb 05 '15 at 11:24
Sorry, i did not mentioned that. I am using SQL Server 2008 R2. Yes I have the back up also — hud, Feb 05 '15 at 11:25
Nadeem: Its good you have backup did you tried restoring it using valid backup. Restore sequence would be full backup backup then differential (if you have) then log backup with stopat clause to stop before table was dropped — Shanky, Feb 05 '15 at 14:04

Racer SQL · Accepted Answer · 2015-02-05T16:38:50.657

4

You can restore the BACKUP with a different name in the same instance and follow steps below.

1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.

2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.

3) Now you can do something like this:

USE [Old Database] 
GO 
SELECT * INTO [the blank table you've created in the new database] 
FROM [the table on the backup, that has Data]

This will copy the data from the backup table, to the table you created.

edited Feb 05 '15 at 16:38

answered Feb 05 '15 at 11:45

Racer SQL

7,386
14
69
128

4

At least a comment with the reason for the Downvote would be great for me. – Racer SQL Feb 05 '15 at 12:19
I did not downvoted. ALso, this is not working for me :) – hud Feb 05 '15 at 12:25
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them. – Racer SQL Feb 05 '15 at 12:26
2

I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup – Shanky Feb 05 '15 at 14:02
@Shanky i will try to explain better. I will update. – Racer SQL Feb 05 '15 at 15:22
@NadeemKhan i updated my step-by-step. I hope it will help you. ( and again, someone downvoted an answer. but why? ) – Racer SQL Feb 05 '15 at 16:39
@RafaelPiccinelli: Yes will wait for your exact answer..:) – hud Feb 06 '15 at 06:03
@Nadeem it's already updated. it solved your problem? i hope so. – Racer SQL Feb 09 '15 at 17:04
@RafaelPiccinelli: I did not tried yet, let me try. Will let you know.!! – hud Feb 10 '15 at 05:21

score 1 · Answer 2 · answered Feb 09 '15 at 06:35

If you backup your transaction log you will be able to restore up to a given point in time using the stopat option.

As previously mentioned you should restore it on a different database.

That way you will be able as much data back as possible.

RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

score 0 · Answer 3 · edited Feb 05 '15 at 17:38

Please follow below mentioned steps:

Restore your DB backup on other server or on same server if it not a production server and you have enough space exist on this server.
Right click on new restored database and select Task --> Export data
select --Source server and database ---> destination server and database
choose your dropped table and export it on required server.

please mentioned the issue which you are facing during the above mentioned activity

Recover deleted table from the database

3 Answers3

Linked