January 10, 2009 at 4:15 am
How do I recover data from SQL Server's log files?
UPDATE foo1 SET relID = 5
After I hit F5, I realize: oh dear, I forgot to add a WHERE clause. I just updated all of the data in my table! I wasn't in a transaction, so I can't roll back. I don't have a backup, so I can't restore. What do I do?
🙂 Ravikumar.R
January 10, 2009 at 12:55 pm
Typically, you don't. The transaction log is not there for data recovery and there's no built-in way (except for ROLLBACK TRANSACTION) to use the transaction log to undo a transaction.
Additionally, if you have absolutely no backup (why?), then regardless of what recovery model you are in, the log will truncate (discard unneeded records) when a checkpoint occurs. Those occur at regular intervals depending on database activity.
If you still want to try, you can buy a log recovery tool (Apex's SQL Log reader or Lumigent's Log Explorer) and see what they can get back for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2009 at 2:58 pm
you can restore the backup in a different box that is taken before the update. and then you can import that table from there.
January 13, 2009 at 10:56 pm
Avinash (1/13/2009)
you can restore the backup in a different box that is taken before the update.
Except that he doesn't have a backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2009 at 3:36 am
redgate SQL Log rescue, will show you all the inserts updates and deletes
January 15, 2009 at 3:48 am
Colum.Whelan (1/15/2009)
redgate SQL Log rescue, will show you all the inserts updates and deletes
From redGate's site:
Please note that SQL Log Rescue does not support 64-bit versions of SQL Server or SQL Server versions other than SQL Server 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2009 at 4:00 am
You could try a log-reader but be aware that these can be quite expensive, and it may hard explaining to your boss why you did not have any backups.
January 16, 2009 at 10:40 pm
Ravikumar.msc (1/10/2009)
How do I recover data from SQL Server's log files?UPDATE foo1 SET relID = 5
After I hit F5, I realize: oh dear, I forgot to add a WHERE clause. I just updated all of the data in my table! I wasn't in a transaction, so I can't roll back. I don't have a backup, so I can't restore. What do I do?
if u had use begin transaction then apply rollback transaction...
otherwise if u take transaction log backup..then restore full bakup with norecovery and apply log with recovery..
January 17, 2009 at 12:51 am
Paresh Prajapati (1/16/2009)
otherwise if u take transaction log backup..then restore full bakup with norecovery and apply log with recovery..
Except that he said he doesn't have a backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply