SQL更新语句问题 [英] SQL update statement issue

问题描述

我有一个问题，在使用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.

How can I get old values??

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

推荐答案

您需要一个SQL事务日志查看器.
好消息是，它们中的大多数都可以通过简单的单击来撤消您提到的最后一个更新命令:
http://www.apexsql.com/sql_tools_log.aspx [ http://www.red-gate.com/products/dba/sql-log-rescue / [ ^ ]

坏消息是它们中的一些非常昂贵，并且其中一些可能与您的SQL Server版本不兼容.

希望对您有所帮助.

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.

糟糕.
从上次备份还原是最好的第一个选择-您定期进行备份，不是吗?
不幸的是，如果您无法还原，则需要从日志文件中还原.是时候开始阅读(并祈祷...) http ://www.techrepublic.com/blog/datacenter/restore-your-sql-server-database-using-transaction-logs/132 [

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[^]

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/

这篇关于SQL更新语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！