How to Rollback a Transaction in SQL Server ?

Andrew Jackson | October 31st, 2015 | SQL Transaction Log |

Introduction

In SQL server each entry in a database count as a transaction. Through these transactions, SQL Server manages its database. Sometime a user performs such tasks in SQL Server that he did not want to perform. To handle such type of actions SQL Server uses some transaction control commands. These commands are used to control the data integrity and take control on database errors. In this article we will discuss one of the transaction control command, ROLLBACK to know how to rollback a transaction and recover deleted records in SQL Server.

How to use Rollback Command?

The ROLLBACK command in SQL Server is generally used to undo the transaction that have not been saved to the database.

SYNTAX

ROLLBACK;

Now, first of all we will create a table and check it by running select statement.

Create Table

After executing the above statement, the data from student table will fetch in our database. Now, I will insert a record in this table by executing insert statement:

Insert Into

The above statement will create a record in student table.

Now I am going to DELETE this record from the student table by executing below statement:

Delete Statement

The above statement will remove the record from student table.

Oops! But there is something wrong with this statement, I forgot to place BEGIN TRANSACTION before above two statements.

BEGIN TRAN:

If you place the BEGIN TRANSACTION before your statement (Insert, Delete,Update), The transaction will automatically turn into the explicit transaction and it will lock the table until the transaction is committed or rolled back.

So, Now I will place the begin transaction before the Insert,Delete,Update statements.

Lets again insert a value in student table

Begin Transaction Insert

Now, executing the delete statement

Begin Transaction Delete

The transaction is now deleted. Since the transaction is locked by BEGIN TRANSACTION, so I can rollback the above deleted record by using the ROLLBACK command.

Rollback Transaction

After executing the ROLLBACK command, and running select statement we see that we have successfully recovered our deleted record.

NOTE: The ROLLBACK command only works until the transaction is not COMMITTED.

Conclusion:

The article discusses on how an uncommitted transaction can be rolled back in such situation, when a user does not want to perform it. But you need to keep in your mind, whenever you perform such task, your transaction must be locked from BEGIN TRANSACTION and the transaction should not be committed. The BEGIN TRANSACTION locks your transaction until you do not rollback or commit your transaction. The long running queries in transaction can result in failed rollback. To prevent dataloss failures,you can use third party SQL Log Reader tool.