且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

帮助 !如何在SQL 2005中恢复表数据

更新时间:2022-11-03 08:53:53


SQL Server会为每次转换保留日志.因此,您也可以从日志中恢复修改后的数据而无需备份.
这是示例源代码.

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.

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-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/


您不能.这就是为什么您应该保留备份的原因.如果更新它们,则旧数据将消失.
You can''t. This is why you should keep backups. If you updated them, the old data is gone.