How can I rollback an UPDATE query in SQL server 2005

Question

How can I rollback an UPDATE query in SQL server 2005   I need to do this in SQL  not through code

User · Answer

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

User · Answer

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  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   ERRORHAND   Success   False   objMyConn RollbackTrans  lt -- here we roll back if error encountered somewhere  LogMessage    ERROR writing database     amp  Err Description

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try  ROLLBACK WORK    It usually works

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2005

Examples related to sql-update

Examples related to rollback