Solved

Get back data using log files

Posted on 2013-01-21
6
318 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 142

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now