Solved

Get back data using log files

Posted on 2013-01-21
6
331 Views
Last Modified: 2013-01-28
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?
0
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38804371
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
 
LVL 12

Accepted Solution

by:
NormanMaina earned 300 total points
ID: 38804372
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38806784
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38814172
@David: lumigent  - seems very interesting. Could you please provide more details on it?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38816031
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
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 38826071
Thanks.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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