CODEWITHSUNDEEP
sqlsql-servertransactions
otto
otto

Reputation: 190

How to revert mistakenly made transactions?

This is a general question but to make it a bite more concrete let us say you have a table like this in SQL Server database:

ORDER (ID, CUSTOMER, ...)

Now you want to do an update on a single row like this:

UPDATE ORDER
SET CUSTOMER = 'NewGuyInTown'
WHERE ID = '1'

By mistake you query only the first two lines so your query looks like this:

UPDATE ORDER
SET CUSTOMER = 'NewGuyInTown'

Result would be, that CUSTOMER of all rows in ORDER will be set to 'NewGuyInTown' of course.

I am happy it did never happen to me but is there a way to find those transactions and do a rollback in SQL Server Management Studio (SSMS)?

Edit

I know you can do

BEGIN TRANSACTION
...
ROLLBACK

to see number of affected rows.

You also can do a backup before update.

But what to do if someone will ever do it as described before and change all the data mistakenly?

Upvotes: 0

Views: 1878

Answers (2)

Vikdor
Vikdor

Reputation: 24134

Unless SET IMPLICIT_TRANSACTIONS checkbox in Options is unselected, SQL command executions will make permanent change to the data in your database. So, there is no way to know the state of the database prior to the execution of the statement or the rows that were affected by the execution, unless you had a backup of the database state prior to the execution of such statements.

If that checkbox is unselected, then you can open another query window and see the state of the records and be able to compare the output in that window with the output in the window where the statements are run. And then, there are a number of Do's and Don'ts on executing SQL statements directly on the database like this to prevent accidental unintended modifications to the data! HTH.

Upvotes: 3

xibalba1
xibalba1

Reputation: 544

You can revert transactions using the ROLLBACK keyword.

The syntax would be:

BEGIN TRANSACTION;
UPDATE ORDER
SET CUSTOMER = 'NewGuyInTown';
ROLLBACK TRANSACTION;

See also: How to roll back UPDATE statement?

Upvotes: 2

Related Questions