恢复更新的数据 - 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.
推荐答案
Hi AngelIII,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://稀有sql.com/2012/02/01/how-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/
相关文章