How to Rollback a Transaction in SQL Server ?
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.
Now, first of all we will create a table and check it by running select statement.
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:
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:
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.
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
Now, executing the delete statement
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.
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.
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.