Solved

Get back data using log files

Posted on 2013-01-21
6
324 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 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