如何在SQL Server 2005中回滚UPDATE查询？

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

如何在SQL Server 2005中回滚UPDATE查询?

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

我需要在SQL中执行此操作,而不是通过代码。

10 个解决方案

#1

begin transaction

// execute SQL code here

rollback transaction

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.

如果您已经执行了查询并希望将其回滚,那么您唯一真正的选择就是恢复数据库备份。如果您使用完全备份,则应该能够将数据库还原到特定时间点。

#2

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

您需要此工具,您可以找到该交易并将其撤消。

ApexSQL Log

#3

You can use implicit transactions for this

您可以为此使用隐式事务

SET IMPLICIT_TRANSACTIONS ON

update Staff set staff_Name='jas' where staff_id=7

ROLLBACK

As you request-- You can SET this setting ( SET IMPLICIT_TRANSACTIONS ON) from a stored procedure by setting that stored procedure as the start up procedure.

根据您的要求 - 您可以通过将该存储过程设置为启动过程,从存储过程设置此设置(SET IMPLICIT_TRANSACTIONS ON)。

But SET IMPLICIT TRANSACTION ON command is connection specific. So any connection other than the one which running the start up stored procedure will not benefit from the setting you set.

但SET IMPLICIT TRANSACTION ON命令是特定于连接的。因此,除了运行启动存储过程的连接之外的任何连接都不会受益于您设置的设置。

#4

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)

(手动修复或恢复备份)

#5

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.

提交更新后,您无法仅回滚单个更新。最好的办法是回滚到以前的数据库备份。

#6

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.

根据您指定的信息,您最好的恢复机会是通过数据库备份。我不认为你能够回滚你所推动的任何改变,因为你当时显然没有使用交易。

#7

Simple to do:

简单易行:

header code...

Set objMyConn = New ADODB.Connection

Set objMyCmd = New ADODB.Command Set

objMyRecordset = New ADODB.Recordset

On Error GoTo ERRORHAND

Working code...

objMyConn.ConnectionString = ConnStr

objMyConn.Open

code....

'Copy Data FROM Excel'

'从Excel复制数据'

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 + 1)

Next NewColumns objMyRecordset.Update Next NewRows

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

objMyConn.Close

ERRORHAND:

Success = False 

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

LogMessage = "ERROR writing database: " & Err.Description

...

#8

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.

虽然我们对您的中级问题没有太多帮助...希望这些答案能够确保您将来不再遇到此问题。

#9

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

在这个例子中,我们在查询中运行2行插入,如果所有这些都为true,则运行但如果不是则不运行任何东西和ROLLBACK

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

#10

Try

ROLLBACK WORK;

It usually works

它通常有效

#1

begin transaction

// execute SQL code here

rollback transaction