How to Rollback a Transaction in SQL Server?
Table of Content
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. Hence, rollback command in SQL server is our todays’ topic to study.
These commands are used to control the data integrity and take control on database errors. In this article we will discuss the transaction control command in depth. Let us know how to rollback SQL transactions and recover deleted records in SQL Server.
How to Rollback in SQL server Quickly
Users just need to understand that SQL Server rollback statement syntax is quite easy to execute. Users need to enter the statement ROLLBACK TRANSACTION with the name of the transaction that is required to roll back. This way users can easily get the desired results they want.
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 for the rollback SQL query, 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, to continue rollback in SQL, 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 the above two statements. Therefore, users need to pay attention to this step for rollback SQL cmd.
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 to further proceeding in rollback in SQL server.
Evidently, 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 SQL command, and running select statement we see that we have successfully recovered our deleted record.
NOTE: The rollback command in SQL 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 SQL Log Reader tool as well. Evidently, this way users can easily get the best-in-class solution without facing any hassles like SQL server rollback command.
Q-1. How do I ROLLBACK changes in SQL server?
Ans: Follow these 7 simple steps –
- State a table variable @TEST to start
- Now, insert a record here to continue
- BEGIN TRANSACTION for explicit transaction
- Now simply update the record in a table variable
- Update that record in the table variable @TEST
- Now just ROLLBACK TRANSACTION to proceed
- Verify the value of the record in the table variable
Q-2. What is the ROLLBACK command in SQL?
Ans: The rollback command is defined as a transaction command that users perform to undo their transactions if they are not saved to the database.
Q-3. How do I ROLLBACK committed data in SQL server?
Ans: Follow the five simple steps to rollback committed data in SQL
- Examine schema & references of current DB
- Now begin a migration transaction
- Set & apply all the required changes
- Verify if the changes are reflecting
- Commit the transaction or revert it.
Q-4. How do I ROLLBACK delete in SQL server?
Ans: Follow these 4 simple steps to ROLLBACK DELETE:
- Execute BEGIN TRANSACTION
- Now perform a transaction & then Delete
- Transaction is locked due to BEGIN TRAN
- Now Perform ROLLBACK TRANSACTION