Get back data using log files

Having said that deleted table using DELETE command could be retrieve back as it is logged. How to achieve that?

Using log files how could we restore to old state?
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
Norman MainaConnect With a Mentor Commented:
If your database is in full recovery mode and the log has not been truncated, you can create a tail log backup and restore the database from the last valid full backup and apply Point in Time Recovery to the database stopping up to the point where the delete happens. I would restore the database to a different database name as the original database may need to stay intact if in the meantime someone entered important data.

connectsql.blogspot.com/2011/01/sql-server-point-in-time-database.html
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
restore the last full back from before that delete to a new database, with eventually some transaction log backups until the point in time before the delete.

you can read the data from that database/table into your production table.

for t-sql help about the restore:
http://www.techrepublic.com/blog/datacenter/restore-your-sql-server-database-using-transaction-logs/132

note that the transaction log backup + restore ONLY works if you have the db in full recovery mode.
if you have the db in simple recovery mode, you can only restore the (last) full backup, and have the data you had in there.
0
 
David ToddSenior DBACommented:
Hi,

Depends somewhat on the meaning of deleted.

Was the table dropped?

Was the table truncated?

Were all rows deleted?

Were some rows deleted?

The real question is, since you found the table was deleted, what else has happened to the database during/since? That is why the recommendation to restore to another database.

There were in time past some tools from the likes of lumigent that could read a transaction log or log backup and show what transactions were happening.

Sometimes a short sharp outage gets things resolved quicker than a long drawn-out picking your way through the logs.

Some of the other factors is: What sort of data is in this table? Is it fairly static or constantly moving (inserts/updates)?

Note that unless you have enterprise edition, and this table is in a small filegroup, you really have to restore the entire database. If it is too big to do this, look at Redgates Virtual Restore. Not sure if you can apply log backups, but might get you moving without having to get more disk to host a second copy of your database.

HTH
  David
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Easwaran ParamasivamAuthor Commented:
@David: lumigent  - seems very interesting. Could you please provide more details on it?
0
 
David ToddSenior DBACommented:
Hi,

lumigent was a long time ago.

Best you google and look for trials of programs that read and replay SQL transaction logs. And look up the usual third party tool providers ...

HTH
  David
0
 
Easwaran ParamasivamAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.