1

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 5 '15 at 11:24
  • Sorry, i did not mentioned that. I am using SQL Server 2008 R2. Yes I have the back up also – B N Feb 5 '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 5 '15 at 14:04
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.

  • 4
    At least a comment with the reason for the Downvote would be great for me. – Racer SQL Feb 5 '15 at 12:19
  • I did not downvoted. ALso, this is not working for me :) – B N Feb 5 '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 5 '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 5 '15 at 14:02
  • @Shanky i will try to explain better. I will update. – Racer SQL Feb 5 '15 at 15:22
1

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';
0

Please follow below mentioned steps:

  1. 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.
  2. Right click on new restored database and select Task --> Export data
  3. select --Source server and database ---> destination server and database
  4. choose your dropped table and export it on required server.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.