恢复更新的数据-SQL Server 2005 [英] Recover Updated Data - SQL Server 2005

问题描述

我有一个问题，在使用sql server 2005时，我执行更新查询时没有where子句(错误)，并且该列的所有原始值都丢失了.

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.

如何获取旧值?

任何建议/提示/解决方案均受到欢迎，将受到高度赞赏.

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

推荐答案

天使III， SQL Server会为每次转换保留日志.因此，您也可以从日志中恢复修改后的数据，而无需备份.

Hi AngelIII, SQL server keeps log for every transation.So you can recover your modified data from the log as well without backup.

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') 

在这里是步枪，它一步一步地解释了如何做到这一点. http://raresql.com/2012/02/01/how-to-to-recover-modified-records-from-sql-server-part-1/

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/

这篇关于恢复更新的数据-SQL Server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！