Click here to Skip to main content
15,899,124 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
By mistake I update some records in sql ..


plz.. help me how to restore it.
Posted

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
 
v2
You can't. This is why you should keep backups. If you updated them, the old data is gone.
 
Share this answer
 

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