How to Rollback Committed Transactions on MS SQL Server

April 21st, 2021

The MS SQL Server database management system suggests that all changes are carried out via transactions. Transactions are created to ensure the ACID requirements:

  • A – Atomicity. All operations in a transaction must either be performed successfully or none of them is to be performed.
  • C – Consistency. The data must be consistent before the launch of a transaction and after its completion.
  • I – Isolation. Active transactions must not impact each other.
  • D – Durability. Committed transactions will be performed, and those not committed will be rolled back under any conditions.

Transactions are also used for the versioning of schemas and referential database data. For instance, they are used during migrations.

Usually, to rollback a migration successfully, we need to deploy it in the following way:

  1. Analyze the current version of the database schema and references.
  2. Open a migration transaction.
  3. Apply all the necessary changes.
  4. Check the changes.
  5. Commit the transaction (if the checks are successful) or revert it.

When developing migration scripts, we also need to create dedicated scripts for reverting that migration. We must ensure the possibility of rolling the migration back even if the data gets changed in the future. Also, we need to change all data related to the changed or added references before we rollback the changes directly.

If possible, we should split the migration into several more autonomous parts that we can check and either commit or rollback. In most cases, it is better to make the migration autonomous and not very large. Several smaller migrations are better than one large migration split into several autonomous migrations.

But what if something goes wrong, and we haven’t provided the redo scripts? And what if we need to understand why the transaction log grows enormously? How can we review the records in the transaction log?

We’ve got a solution: dbForge Transaction Log, a SQL Server tool that helps read the transaction log as well as rollback selected transactions or generate scripts for them.

Let’s take an example: a JobEmplDB database for a recruitment service.

Picture 1. Recruitment service database schema

Reading the Transaction Log

To read the transaction log, we need to take the following steps:

1. Click New Transaction Log Project.

Picture 2. The main window of dbForge Transaction Log

2. Select the required mode for the transaction log.

Picture 3. Available transaction log modes

2.1. Offline mode is suitable for working with the transaction log of a detached database. Here, we only need to select the data files and the transaction log files.

Picture 4. Selecting database files in the offline mode

2.2. Online VSS mode works with a shadow copy of the database transaction log. Here, we select the connection and the database.

Picture 5. Selecting the connection and the database in the shadow copy mode

2.3. Online NTFS mode enables work with the transaction log of an online database (we also select the connection and the database).

Picture 6. Selecting the connection and the online database

In our example, we will stick to the Online NTFS mode. Here we select the necessary connection and the database, then click Next in the bottom right corner of the screen.

Picture 7. Selecting the parameters for the transaction log

Then we need to select the appropriate parameters to filter the required transactions from the transaction log. In the filters, we can set the time and date range, as well as select the types of DML statements (INSERT, UPDATE, and DELETE).

Picture 8. Selecting the filters

4. After configuring all the required filters, click Next in the bottom right corner of the screen to start data collection.

Picture 9. Data collection

5. When the process is over, we see the window with filtered transactions triggered by DML statements. The list of transactions is on the top pane. The detailed information for each selected transaction is provided on the bottom pane.

Picture 10. The window with filtered transactions

Generating and Exporting Redo and Undo Scripts

The Export Script menu has two options:

  • Change Options adjusts filtering parameters for transactions
  • Export Script generates undo/redo scripts for selected changes
Picture 11. Exporting undo/redo scripts for selected changes

If we choose to export a redo script, we will get the following:

Picture 12. The redo script generated

And if we choose to export an undo script, we will get the following:

Picture 13. The undo script generated

These scripts apply to both rolling back the current changes and to redoing changes in a similar database.

Note: To redo changes, the state of the database must allow executing the script.

Now, let’s get back to the filtered transactions window and examine the table of selected transactions. We will see that we can perform sorting by any column. Also, the column menu offers a number of other operations, as shown on the following screenshot.

Picture 14. Available operations with data in the table of selected transactions

We can search through the selected transactions using the CTRL+F combination:

Picture 15. Searching through the selected transactions

On the Properties tab, we can see the main properties of the selected transaction.

Picture 16. Transaction properties

On the Redo Script tab, we can view the script to redo changes (the entire transaction code):

Picture 17. The redo script for a transaction

The Undo Script tab presents the script to undo changes. It is a kind of transaction rollback simulation.

Picture 18. The undo script for a transaction

Conclusion

You might want to undo your last SQL query for a number of reasons, and this is how dbForge Transaction Log helps you do it easily. With this tool, you can read your transaction log quickly, even online, and without database shadow copying. When the transaction log keeps growing enormously, the tool helps reveal the reason why it does so. It is also helpful when you need to generate scripts to redo and undo selected changes for your DML operations.

Evgeniy Gribkov

Comments are closed.