Solved

Get back data using log files

Posted on 2013-01-21
6
314 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

15 Experts available now in Live!

Get 1:1 Help Now