blocks|key|1373396|text|begin+transaction

//+execute+SQL+code+here

rollback+transaction|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1373397|如果您已经执行了查询，并且想要回滚它，不幸的是，您唯一真正的选择是恢复数据库备份。如果您使用的是完整备份，那么您应该能够将数据库恢复到特定的时间点。|unstyled|1373398|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>begin transaction

// execute SQL code here

rollback transaction
</code></pre>

If you've already executed the query and want to roll it back, unfortunately your only real option is to restore a database backup. If you're using Full backups, then you should be able to restore the database to a specific point in time.

blocks|key|1588783|text|你需要这个工具，你可以找到交易并撤销它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1588784|ApexSQL+Log|offset|length|1588785|entityMap|0|LINK|mutability|MUTABLE|url|http://www.apexsql.com/sql_tools_log.asp?gclid=COWEvOex3JkCFQMQswod4RgXXA^0|0|0|B|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You need this tool and you can find the transaction and reverse it.

<a href="http://www.apexsql.com/sql_tools_log.asp?gclid=COWEvOex3JkCFQMQswod4RgXXA" rel="noreferrer">ApexSQL Log</a>

blocks|key|1588430|text|您可以回滚在事务中执行的语句。回滚事务，而不是提交事务。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1588431|如果你已经更新了一些东西，并且想要回滚这些更新，而你还没有在一个(尚未提交的)事务中做到这一点，那么我认为这是幸运的…|1588432|(手动修复或还原备份)|1588433|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

You can rollback the statements you've executed within a transaction.
Instead of commiting the transaction, rollback the transaction.

If you have updated something and want to rollback those updates, and you haven't done this inside a (not-yet-commited) transaction, then I think it's though luck ...

(Manually repair, or, restore backups)

blocks|key|1373447|text|一旦提交了更新，就不能只回滚单个更新。最好的办法是回滚到数据库的上一次备份。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373448|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Once an update is committed you can't rollback just the single update. Your best bet is to roll back to a previous backup of the database.

blocks|key|1373662|text|根据您指定的信息，最好的恢复机会是通过数据库备份。我不认为您将能够回滚您推送的任何更改，因为您当时显然没有使用事务。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373663|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

From the information you have specified, your best chance of recovery is through a database backup. I don't think you're going to be able to rollback any of those changes you pushed through since you were apparently not using transactions at the time.

blocks|key|1373823|text|简单操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373824|标题代码...|1373825|Set+objMyConn+=+New+ADODB.Connection

Set+objMyCmd+=+New+ADODB.Command+Set

objMyRecordset+=+New+ADODB.Recordset

On+Error+GoTo+ERRORHAND+|code-block|syntax|javascript|1373826|工作代码..。|1373827|objMyConn.ConnectionString+=+ConnStr

objMyConn.Open+|1373828|代码...|1373829|‘从Excel复制数据’|1373830|objMyConn.BeginTrans+<--+define+transactions+to+possible+be+rolled+back+

For+NewRows+=+2+To+Rows

objMyRecordset.AddNew+

For+NewColumns+=+0+To+Columns+-+1

objMyRecordset.Fields(NewColumns).Value+=+ActiveSheet.Cells(NewRows,+NewColumns+%2B+1)

Next+NewColumns+objMyRecordset.Update+Next+NewRows

objMyConn.CommitTrans+<-+if+success,+commit+them+to+DB

objMyConn.Close|1373831|错误：|1373832|Success+=+False+

objMyConn.RollbackTrans+<--+here+we+roll+back+if+error+encountered+somewhere

LogMessage+=+"ERROR+writing+database:+"+&+Err.Description|1373833|..。|1373834|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|17|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|19|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

Simple to do: 

header code...

<pre><code>Set objMyConn = New ADODB.Connection

Set objMyCmd = New ADODB.Command Set

objMyRecordset = New ADODB.Recordset

On Error GoTo ERRORHAND 
</code></pre>

Working code... 

<pre><code>objMyConn.ConnectionString = ConnStr

objMyConn.Open 
</code></pre>

code.... 

'Copy Data FROM Excel'

<pre><code>objMyConn.BeginTrans &lt;-- define transactions to possible be rolled back 

For NewRows = 2 To Rows

objMyRecordset.AddNew 

For NewColumns = 0 To Columns - 1

objMyRecordset.Fields(NewColumns).Value = ActiveSheet.Cells(NewRows, NewColumns + 1)

Next NewColumns objMyRecordset.Update Next NewRows

objMyConn.CommitTrans &lt;- if success, commit them to DB

objMyConn.Close
</code></pre>

ERRORHAND:

<pre><code>Success = False 

objMyConn.RollbackTrans &lt;-- here we roll back if error encountered somewhere

LogMessage = "ERROR writing database: " &amp; Err.Description
</code></pre>

...

blocks|key|1373759|text|如前所述，除了从备份还原之外，您什么也做不了。至少现在，您将学会总是将语句包装在事务中，以便在决定提交之前查看发生了什么。此外，如果你没有备份你的数据库，这也会教你定期备份你的数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373760|虽然我们对您的即时problem...hopefully帮助不大，但这些答案将确保您在未来不会再次遇到此问题。|1373761|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

As already stated there is nothing you can do except restore from a backup. At least now you will have learned to always wrap statements in a transaction to see what happens before you decide to commit. Also, if you don't have a backup of your database this will also teach you to make regular backups of your database. 

While we haven't been much help for your imediate problem...hopefully these answers will ensure you don't run into this problem again in the future.

blocks|key|1373908|text|在本例中，我们运行两行insert+into+query，如果全部为真，则运行，否则运行任何内容并回滚|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373909|DECLARE+@rowcount+int++set+@rowcount+=+0+;+
BEGIN+TRANSACTION+[Tran1]
BEGIN+TRY+
+insert+into+[database].[dbo].[tbl1]+(fld1)+values('1')+;
++++set+@rowcount+=+(@rowcount+%2B+@@ROWCOUNT);+
+insert+into+[database].[dbo].[tbl2]+(fld1)+values('2')+;
++++set+@rowcount+=+(@rowcount+%2B+@@ROWCOUNT);+

IF+@rowcount+=++2
++COMMIT+TRANSACTION[Tran1]
ELSE
++ROLLBACK+TRANSACTION[Tran1]
END+TRY
++BEGIN+CATCH
++ROLLBACK+TRANSACTION[Tran1]
END+CATCH|code-block|syntax|javascript|1373910|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

in this example we run 2 line insert into query and if all of them true it run but if not no run anything and ROLLBACK 

<pre><code>DECLARE @rowcount int set @rowcount = 0 ; 
BEGIN TRANSACTION [Tran1]
BEGIN TRY 
 insert into [database].[dbo].[tbl1] (fld1) values('1') ;
 set @rowcount = (@rowcount + @@ROWCOUNT); 
 insert into [database].[dbo].[tbl2] (fld1) values('2') ;
 set @rowcount = (@rowcount + @@ROWCOUNT); 

IF @rowcount = 2
 COMMIT TRANSACTION[Tran1]
ELSE
 ROLLBACK TRANSACTION[Tran1]
END TRY
 BEGIN CATCH
 ROLLBACK TRANSACTION[Tran1]
END CATCH
</code></pre>

blocks|key|1373777|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1373778|ROLLBACK+WORK;|code-block|syntax|javascript|1373779|它通常是有效的|1373780|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try

<pre><code>ROLLBACK WORK;
</code></pre>

It usually works

How can I rollback an UPDATE query in SQL server 2005?

I need to do this in SQL, not through code.

How can I rollback an UPDATE query in SQL server 2005?

数据库

如何在SQL server2005中回滚更新查询？我需要用SQL来做这件事，而不是通过代码。

问如何在SQL server2005中回滚更新查询？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL server2005中回滚更新查询？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL server2005中回滚更新查询？
EN