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

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

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

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.

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'

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

...

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

ApexSQL Log

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.

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.

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

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

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.

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)

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.

Try

ROLLBACK WORK;

It usually works