Log Explorer instructions

I. Introduction

Log Explorer is mainly used for MSSQLServer transaction analysis and data recovery. You can view logs, export data, and restore modified or deleted data (including tables that have executed update, delete, drop, and truncate statements ). Once data is lost due to system faults or human factors, it can provide online and fast data recovery to maximize the uninterrupted execution of other things during recovery.

It supports sqlserver7.0 and sqlserver2000 to extract the log files of the standard database or the information in the backup files.

 

Two powerful tools are provided: Log Analysis and browsing, and object recovery. The specific functions are as follows:

• Log File browsing
• Database Change Review
• Plan and authorize change Review
• Export log records to files or database tables
• Monitor database transactions in real time
• Calculate and count Loads
• Recover data by selectively canceling or redoing things
• Restore truncated or deleted table data
• Run the SQL script

Ii. Installation

Logtaile contains two parts:

• Client Software
• Server proxy

 

 

The server-side proxy is a read-only stored procedure on the sqlserver host. Its role is to accept client requests, read online transaction log blocks, and transmit them to the client software through the network, the client software reads these raw data blocks to complete all the functions provided by log volume E.

The dependent network protocols include:

• Named Pipe: Applicable in LAN
• TCP/IP: Applicable to Wide Area Networks

 

This software is in the C/S mode. The server is installed on the MSSQL Server, and the client can be installed as needed.

 

Iii. Instructions for use 3.1 connect to the database

Double-click log consumer e to display the Software Interface

 

 

Click attach log file in the upper-left corner to go to the database connection page.

 

Note:

 

 

Select the database name you want to view and whether to use online logs or backup files for analysis.

 

After successfully connecting to the database, a warning is displayed. Press OK.

 

3.2 Function Description

 

 

Log summary:

The summary of the log file.

 

Load Analysis:

Lists the objects in a specified time range, the summary of user and table loading.

 

Filter log record:

LOG filter settings. Supported filtering conditions include: time, operation type, table, user, spid, search depth, dropped table item, login setting, and application setting

 

Browse:

Log browsing, the core module.

 

• View log function:
  List. You can use transid to distinguish objects and use different colors. The buttons in the toolbar are some basic query operations. Right-click the Undo transaction and undooperation items in the pop-up menu to restore the items or operation items selected by the Black Arrow.
• Real-time monitor:
  Real-time Monitoring of transaction logs is achieved through polling. You can pause or stop monitoring and change the polling cycle.
• Related DML and DDL languages can be queried in row revision history, row transaction history, and view DDL commands.

Export log records:

• Export to SQL

Export data from logs and restore data to the SQL database table

• Export to files

Export data from logs and save it as an XML, HTML, or character segmentation File

 

Recovery: Undo user transactions:

You can cancel a series of selected transactions, such as deleting or modifying data due to user or application errors.

 

Recovery: redo user transactions:

Allow you to redo a series of selected transactions.

 

Salvage dropped/truncated data:

Allows you to recover part or all of the data. For a deleted or truncate table

 

Restore table from backup:

Use Backup to restore table data

 

Run SQL script:

Allows you to execute SQL commands stored in the script file.

 

Exit:

Exit Log Explorer

 

Iv. Data Recovery example 4.1 accidental data deletion

To simulate the accidental deletion of data, the test environment deletes two rows of data in the test table in the test database.

 

 

4.2 transaction search

Use Log Explorer to find the transaction logs of data deletion operations. On the view log, we can see the two delete_rows operations of the test table. That is to say, the two rows of data with 4 and 5 in the name column have been deleted.

 

4.3 Data Recovery

Right-click the transaction operation to be restored and select undo transaction.

 

A rollback file storage box appears. The text contains the SQL statement to be restored.

 

Copy the content of the recovery1. SQL file to the query analyzer for restoration.

 

V. Introduction to the database: transaction log 5.1)

 

Every SQL Server database contains transaction logs, which are stored as files and can record any changes to the database. When a fault occurs, sqlserver uses it to ensure data integrity.

 

5.2 operation)

An operation is an "atomic action" defined in the database. Each operation is saved as a record in the log file. It can be an SQL statement directly entered by the user, such as the standard INSERT command. An operation code is recorded in the log file to mark the insert operation.

 

5.3 transaction)

A transaction is a sequence of operations. It can be understood as an intuitive and inseparable business that can be executed successfully or fail. A typical transaction is a set of SQL statements issued by an application with the Enable-submit function. Different things are differentiated by the transaction ID, and the logs of things with the same ID are also the same.

 

5.4 online transaction log)

Online Transaction logs are the logs used by the active database. You can use the following command to determine the corresponding file:

Select * From sysfiles

The file suffix is generally. LDF.

 

5.5 offline transaction log)

Offline transaction logs are used by non-active databases. The database can be copied and backed up only when it is shut down. The results are exactly the same as those in online transaction logs.

 

5.6 backup files

A backup file is a file that saves the food log backup. Generally, the Administrator runs an SQL statement or the enterprise manager to generate the file. The internal structure of the backup file is different from the transaction log. It uses the format called MTF to store data. A backup file can contain multiple groups of backups with one log, or even a hybrid backup of multiple databases.

 

5.7 set to auto contract

Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"

We strongly recommend that you do not select this option. Otherwise, sqlserver will overwrite the previous log records in a circular manner, which will cause logtaile to fail to recover the error.

 

 

5.8 data recovery

Logtaile allows you to recover data loss or changes caused by misoperations or program errors. For example, the where clause is lost when the update/delete statement is executed, or the DTs function is used incorrectly.

Logtaile does not support direct database modification. It can generate reverse operation scripts for transactions.

If log is delete table where..., the generated file code is Insert table ....

You can use the SQL query analyzer or the run SQL Script Function of logtaile to execute the generated script.

 

5.9 about undo

The Undo function allows you to reverse a group of specified user events. Including insert, delete, and update. Its limitations are as follows:

• Transaction category: logtaile only supports undo user transactions. A user transaction is a transaction defined on a user table. It does not support system table update and recovery. At the same time, he does not support rollback of planned changes.
• Blob type: including text, ntext, and image. Logtaile only supports insert and delete recovery, but does not support update statement recovery.

 

5.10 redo

The redo function can run a group of specified items again. It can be used in the following scenarios:

The database is lost without any backup files.

• If the raw log file is not lost, you can use redo to restore it.
• Use the full backup file to restore the database to a specified time point, and then use the redo function to completely restore the database. It can replay the create table and create index commands to regenerate the deleted table, and is also restricted by BLOB fields.

 

5.11 SQL Inverse Operation

1. Insert -- delete

2. Delete -- insert

3. Update

 

Column1	Column2
A	B

Things 1

Column1	Column2
X	B

Things 2

Column1	Column2
Z	T

You can only perform inverse operations on thing 1.

Column1	Column2
A	T

Note: If you select the 'do not restore column values that have been changed by subsequent modifications 'item, only reversing transaction 1 will not produce any results.

5.12 data loss caused by saving the dropped/truncate command

Running the drop table and truncate table commands will be recorded in the log file by sqlserver, but will not record the deleted data. You can use the functions provided by logtaile to restore the data. Logtaile provides two mechanisms to restore dropped or truncate data.

1. If you have a backup file, you can recover it directly through the backup file.

2. Use the method provided by logtaile to restore data.

When you execute the preceding command, sqlserver puts the page for saving data into the list of idle pages. If this page is not used again, the original data is always saved. During restoration, logtaile searches for unused pages from the free page list and generates an SQL script to reorganize the original data from these pages. Logtaile can identify the deleted raw data rows, and display the original number of rows and the number of actually recovered rows when the deletion is completed. Therefore, it can determine whether all rows are restored.