How to Read Ms Sql Transaction Log File
Just like any information system, databases are constantly irresolute. Quite oft y'all demand to roll back some operations. In that location is undoubtedly an opportunity to restore the database to a specific point in time and you lot can too undo the latest transactions with both regular and third-party utilities.
However, sometimes information technology is necessary to cancel the transactions within a certain period of time.
When that's the case, it is useful to have a user-friendly GUI tool to view the SQL Server transaction log.
Apart from rolling back changes, you may demand 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.
Y'all may likewise need to repeat the transactions on a database in case of data impairment or data loss.
In this article, we will consider the SQL Server transaction log reading for the post-obit purposes:
- Rolling back changes
- Replicating changes to another database.
To view the transaction log, we will use the Transaction Log Reader Tool. Allow's look at this tool closer considering the example of a database for a recruiting agency:
Fig. 1. A schema of a recruiting agency database
Information technology is worth noting that with the electric current functionality of the tool you lot can view a transaction log simply 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 dispense the transactions.
Reading the SQL Server transaction log for rolling back changes
Stride 1. Permit's create a new project by clicking the New button.
Fig. 2. Creating a new project
Footstep 2. We volition select the database .mdf file to read the log grade and click the Adjacent button.
Fig. 3. Selecting a .mdf database data file
Step three. Now you need to set up the time range for the operations and select the types of operations (INSERT, UPDATE, DELETE) to customize the log output. In our case, nosotros are concerned with the entire fourth dimension range and all types of operations. Having fabricated the necessary configuration settings, click the Stop button.
Fig. iv. Configuring options to customize the log output
The screen will brandish the Transaction Log loading window showing the process of collecting information according to the configured options.
Fig. 5. The process of collecting transaction log statistics according to the configured options
In one case the information is processed, the following window displaying the project yous've created will announced.
Fig. 6. A project for finding database operations in a transaction log
Footstep 4. In the project window, y'all can change the transaction selection settings past clicking the Modify Options button.
Fig. 7. Settings for finding operations in a database transaction log
Now let's consider the project window in more item.
Fig. 8. Project window
As you can see, the project window displays a tabular array and the details for each selected row.
The details include:
1) Properties that describe the transaction itself.
Fig. 9. Backdrop of the transaction
2) T-SQL transaction script.
Fig. 10. T-SQL transaction script
3) T-SQL script to undo the transaction.
Fig. 11. T-SQL script to undo the transaction
Step 5. Now we will sort out merely committed transactions by selecting the Committed selection in the filter for the Transaction Status column.
Fig. 12. Selecting committed transactions
Footstep six. In the filter for the Object Name cavalcade, we will select Employee, thereby sorting out only those transactions that are associated with the Employee table.
Fig. xiii. Selecting the transactions associated with the Employee tabular array
Thus, we go all committed transactions associated with the Employee table.
Step 7. Now we can generate a script to gyre back all committed transactions associated with the Employee table past clicking the Export Script button and then selecting the Export Disengage Script selection from the drop-down card.
Fig. xiv. Generating a script to roll dorsum all committed transactions associated with the Employee table
Pace 8. The script nosotros demand will be displayed. You tin can use it to gyre back the changes to the current database.
Fig. 15. Generated script to undo the changes
Read SQL transaction log files for replicating changes to some other database
Similarly, you can generate a script that was used to create the transactions. You may demand it to replicate the transactions to another database. To do this, click the Export Script push and then select the Export Redo Script option from the drop-downwardly menu.
Fig.16. Generating a script to replicate all committed transactions associated with the Employee table
Conclusion
Thus, the generated scripts allow you lot 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 information 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, Change, DROP). This will allow you to create, modify and delete such database objects every bit:
- tables
- indexes
- keys
- constraints
- triggers
- functions
- stored procedures
- views
etc.
- Author
- Recent Posts
abrahamshookeplise79.blogspot.com
Source: https://blog.devart.com/view-the-sql-server-transaction-log-with-sql-transaction-log-reader-tool.html
0 Response to "How to Read Ms Sql Transaction Log File"
Post a Comment