How to view the SQL Server transaction log with SQL Transaction Log Reader Tool

April 30th, 2020

Just like any information system, databases are constantly changing. Quite often you need to roll back some operations. There is undoubtedly an opportunity to restore the database to a specific point in time and you can also undo the latest transactions with both regular and third-party utilities.

However, sometimes it is necessary to cancel the transactions within a certain period of time.
When that’s the case, it is useful to have a convenient GUI tool to view the SQL Server transaction log.

Apart from rolling back changes, you may need to replicate certain transactions to other databases. This is especially true for development and testing environments, as well as for replicated databases in case of loss of replication for a certain point in time or certain transactions.
You may also need to repeat the transactions on a database in case of data damage or data loss.


In this article, we will consider the SQL Server transaction log reading for the following purposes:

  1. rolling back changes
  2. replicating changes to another database.

To view the transaction log, we will use the Transaction Log Reader Tool. Let’s look at this tool closer considering the example of a database for a recruiting agency:

schema of a recruiting agency database

Fig. 1. A schema of a recruiting agency database

It is worth noting that with the current functionality of the tool you can view a transaction log only offline. The online operating mode is under development. To read the transaction log, you need to stop the MS SQL Server service and copy the data files and the database transaction logs. After that, you need to enable the MS SQL Server service and attach the files you’ve copied to another MS SQL Server instance, where you can manipulate the transactions.

Reading the SQL Server transaction log for rolling back changes

Step 1. Let’s create a new project by clicking the New button.

creating a new project

Fig. 2. Creating a new project

Step 2. We will select the database .mdf file to read the log form and click the Next button.

selecting an .mdf file

Fig. 3. Selecting a .mdf database data file

Step 3. Now you need to set the time range for the operations and select the types of operations (INSERT, UPDATE, DELETE) to customize the log output. In our case, we are concerned with the entire time range and all types of operations. Having made the necessary configuration settings, click the Finish button.

customizing the log output

Fig. 4. Configuring options to customize the log output

The screen will display the Transaction Log loading window showing the process of collecting information according to the configured options.

loading the transaction log

Fig. 5. The process of collecting transaction log statistics according to the configured options

Once the data is processed, the following window displaying the project you’ve created will appear.

opened transaction log

Fig. 6. A project for finding database operations in a transaction log

Step 4. In the project window, you can change the transaction selection settings by clicking the Change Options button.

modifying the transaction log

Fig. 7. Settings for finding operations in a database transaction log

Now let’s consider the project window in more detail.

project window

Fig. 8. Project window

As you can see, the project window displays a table and the details for each selected row.

The details include:

1) Properties that describe the transaction itself.

properties of a transaction

Fig. 9. Properties of the transaction

2) T-SQL transaction script.

t-sql transaction script

Fig. 10. T-SQL transaction script

3) T-SQL script to undo the transaction.

undo transaction script

Fig. 11. T-SQL script to undo the transaction

Step 5. Now we will sort out only committed transactions by selecting the Committed option in the filter for the Transaction Status column.

selecting committed transactions

Fig. 12. Selecting committed transactions

Step 6. In the filter for the Object Name column, we will select Employee, thereby sorting out only those transactions that are associated with the Employee table.

selecting committed transactions for the employee table

Fig. 13. Selecting the transactions associated with the Employee table

Thus, we get all committed transactions associated with the Employee table.

Step 7. Now we can generate a script to roll back all committed transactions associated with the Employee table by clicking the Export Script button and then selecting the Export Undo Script option from the drop-down menu.

exporting the undo script

Fig. 14. Generating a script to roll back all committed transactions associated with the Employee table

Step 8. The script we need will be displayed. You can use it to roll back the changes to the current database.

generated undo script

Fig. 15. Generated script to undo the changes

Read SQL transaction log files for replicating changes to another database

Similarly, you can generate a script that was used to create the transactions. You may need it to replicate the transactions to another database. To do this, click the Export Script button and then select the Export Redo Script option from the drop-down menu.

exporting the redo script

Fig.16. Generating a script to replicate all committed transactions associated with the Employee table

Conclusion

Thus, the generated scripts allow you to change the previously committed SQL Server transactions and replicate those transactions in case of data loss. Also, the scripts let you move the changes to other databases with minor modifications. For example, if there is already data to replicate transactions, or if data has already been deleted to roll back transactions.

The future versions of the dbForge Transaction Log will feature an opportunity to view logs not only for DML transactions (INSERT, UPDATE, DELETE, MERGE) but also for DDL transactions (CREATE, ALTER, DROP). This will allow you to create, modify and delete such database objects as:

  1. tables
  2. indexes
  3. keys
  4. constraints
  5. triggers
  6. functions
  7. stored procedures
  8. views

etc.

Leave a Comment