How to Rollback an Update Query in SQL Server 2005

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

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.

How to undo a SQL Server UPDATE query?

A non-committed transaction can be reverted by issuing the command ROLLBACK

But if you are running in auto-commit mode there is nothing you can do....

How do I undo an update statement I made to a database

There is a non-secret transaction log called transaction log that you can recover from to a point in time. Here's how... That annoying little file with the ldf extension is the transaction log, as opposed to the .mdf file that is your normal db data.

Unless you have truncated the transaction log (ldf) or otherwise mucked with it, you should be able to do exactly the kind of restore (undo) that you're looking for.

SQL update undo

You can't unless you ran it inside a transaction.

Depending on your DBMS transactions will be handled differently, so read the documentation. For instance with mysql here's how it goes:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With Postresql:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

With TSQL:

DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'
BEGIN TRANSACTION @TranName
GO
USE pubs
GO
UPDATE roysched
SET royalty = royalty * 1.10
WHERE title_id LIKE 'Pc%'
GO
COMMIT TRANSACTION MyTransaction
GO

There are also some actions that can't be put into a transaction, but in most of the database management systems you can find these days you should be able to rollback an update.

Finally you can always undo an update if you keep a precise log of everything that happens in the database, but that's another story.

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

From what I know there is nothing new here. MS is focusing on enterprise level functionality in SQL Server (in memory queries, more high availability and such).

As far as I know your options to undo accidental update are:

Third party log readers that do cost some $ (I know about Apex Log and Quest Toad but there may be others too)
Restore latest backup in another database and check the difference between databases
Spend a ton of time trying to read transaction log using fn_dblog function like shown here

how to rollback committed data in sql server 2005

what i have to do is restore backup nothing else.

How to kill/resolve a reeeeally long-running update in SQL Server

So we got fed up with waiting for our transaction to complete, (after a full week on
one piece of SQL, who wouldn't?), and as it was interfering with our backup
process, we thought killing it was a necessary evil.

The database started to rollback the transaction.

5 days passed.

We noted with some posts elsewhere on the internet that sometimes some magic
happened when the database was restarted and the transaction would "go away",
although these are generally debunked*, and it makes no sense, we thought we
had nothing left to lose so we gave it a go. We knew the database would go into
recovery mode, but the database was becoming increasingly sick anyway and unable
to run anything but its current rollback work anyway, and we've seen SQL Server misbehave with hogging system resources and not diverting them to where it needs to do the work.

(* we also know enough database theory to know that the DB wouldn't just "forget"
about a transaction in progress, but we were also seeing stack dumps in the
SQL Server error logs which kind of told us that the SQL Server was getting
increasingly grumpy at the amount of rollback it was having to undertake)

So we restarted the database.

Sure enough the database went into recovery mode. However, the SQL Server event Log
was now giving us an update every 20 seconds or so as to how long it was going to
take (in all, it reckoned about 25 hours from the log messages, but it ended up being
just an hour and a half (!)).

Whether this method of recovery/rollback is faster, I would strongly doubt (as I expect
SQL Server had to do the same level of work to unwind the transaction as before), however it did finish within an hour and a half, either way, I don't want to make a habit of restarting my production database when it is halfway through a rollback). The update messages in the event log were an absolute godsend, as anyone who has written a batch program
will tell you; however inaccurate they turned out to be - at least they were a worst case.

As we had the luxury of being the only two people using this production box, choosing to
send the database into recovery mode worked for us, and gave us informational messages we
didn't have access to with just our previous rollback state (or at least nothing we could
interpret given our lacking DBA skills). Would I recommend doing this in future?
....Absolutely not, however, hopefully the concerned parties have learnt their lesson, and
we can ask the board for some money for a proper development server! (epic Joel-Test fail!)

Update query cancelled by user

A single update statement will not update some rows. It's all rows or none

This is the atomicity in the ACID properties which SQL server respects well.

Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.

Then the commit is at the end of the statement, so when you cancel there's no commit