Click here to Skip to main content
16,019,018 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
I have an issue that, while using sql server 2005 i have executed update query without where clause (by mistake) and all the original values of the column get lost.

How can I get old values??

Any suggestion/tips/solution is welcome and will be highly appreciated.
Posted

You need a SQL Transaction Log viewer.
Good news is that most of them are capable of undoing your mentioned last update command within a simple click:
http://www.apexsql.com/sql_tools_log.aspx[^]

http://www.red-gate.com/products/dba/sql-log-rescue/[^]

Bad news is that some of them are very expensive and some of them may not compatible with your SQL server version.

Hope it helps.
 
Share this answer
 
Comments
thatraja 29-Jan-12 12:34pm    
Nice tools, 5!
Amir Mahfoozi 29-Jan-12 13:26pm    
Thanks thatraja :)
Oops.
Restore from your last backup is the first, best option - you do backup regularly, don't you?
Unfortunately, if you can't restore, then you need to restore from log files. Time to start reading (and praying...) http://www.techrepublic.com/blog/datacenter/restore-your-sql-server-database-using-transaction-logs/132[^]
 
Share this answer
 
Comments
Shahan Ayyub 29-Jan-12 9:30am    
I maintains backup but I did not took backup since last week and I have a dozen of updates in it within this time period. So is it possible to get the data back with your suggested solution ?
OriginalGriff 29-Jan-12 9:41am    
It may be - start reading, but don't expect it to be simple!
thatraja 29-Jan-12 12:33pm    
Right, 5!
Hi,
SQL server keeps log for every transation.So you can recover your modified data from the log as well without backup.
Here is the sample source code.
SQL
Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID] ,[RowLog Contents 0]
, [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4] ,[Log Record]
FROM    sys.fn_dblog(NULL, NULL)
WHERE AllocUnitId IN
(Select [Allocation_unit_id] from sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2  AND partitions.partition_id = allocunits.container_id)
Where object_id=object_ID('' + 'dbo.student' + ''))
AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')
And [Context] IN   ('LCX_HEAP','LCX_CLUSTERED')

Here is the artcile, that explains step by step, how to do it.
http://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/
 
Share this answer
 
Comments
Amir Mahfoozi 5-Feb-12 7:05am    
+1000 if it was possible. The article was fantastic :o
Shahan Ayyub 12-Feb-12 4:01am    
Superb Imran. This is what I need.

Thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900