Use log to recover data skillfully

SQL server--to recover data with logs

The SQL Server database generates the database name _log in addition to the data file that generates the database name _data.mdf when it is created. LDF "file. What should we do when we mistakenly delete information from the system tables in the database that results in an inability to use them properly or delete useful data incorrectly? System tables are more threatening because once the deletion relies on SQL Server's own Enterprise Manager, Query Analyzer, and so on, it will not work. The underlying API or DLL driver requires authentication of identity permissions. Temporarily from the MDF file itself, because can not get more about the definition of the file header, if you can know the physical storage of data, completely without verification, directly navigate to the record block to add data. But there is no more effective way to do it, after all, it is a commercial software that can only be used in a transparent way.

A lot of data on the Internet is not practical to delete important users of the system user table operation methods. Because you do not have permission to do any operation. All kinds of inserting statements have lost their value. This can only depend on inserting during memory-resident periods without restarting SQL Server. Therefore, in order to protect your data, I suggest that you, on a regular basis, back up important data, for a large amount of data should be kept at least one week of valid backups.

Now, with the help of a friend, we can use a commercial software log Explorer to analyze the log and then use it to generate a reusable SQL script or undo a rollback script. However, it is important to note that the wrong action you need to remove from the script, you must be particularly cautious about each delete from statement. For error deletion system tables are generally at the end of the file. You may delete a record of sysusers, but the records of related child tables connected to it will also be deleted. Be sure to make the selection carefully and eliminate the dangerous statements.

This software installation is very simple. A serial number must be entered during installation to continue the installation. After the installation is complete, we find Log Explorer to start from the program group.

Enter the start interface as shown in Figure 1, first point to the left navigation of the Attach Log file (bind log files), will appear on the right landing window, mainly set up the SQL Server server connection parameters, SQL Server column Select server name will generally be automatically listed, use Windows authentication (using Windows authentication) does not select the authentication method that will use SQL Server itself, the Login name (SQL Server Admin account name defaults to SA), Password (SQL Server Admin account password), when confirmed, press Connect to connect.

Login success will appear in Figure 2 interface, select the bound log file (support format bak, TRN, LDF). Bak is not generally meaningful to extract from a backup file. Using use Backup file (using Backup files) We do not use on-line log (using the online log). Use the online logs to efficiently retrieve the databases in the database name (database name) list to facilitate statistical analysis. And here we use a local LDF file that's out of SQL Server, not dependent on the SQL Server server, and if you need to detach on the node that appears on the SQL Server service, an error occurs. By using the ellipsis browse button next to select Backup files to start adding files, you can add multiple files and, if it is an incremental backup, make sure that you add it in chronological order, or the resulting SQL script may be wrong. Once the confirmation is correct, press attach (binding) to enter the interface of figure three.

We need the log file to filter the parameter settings. Assuming that the backup was done N days ago, our start time would need to be reset to the time of the backup. If we need to undo the operation within one hour of the failure, the end time needs to be advanced. Can also be based on different operations for a variety of filters, set up after the point apply (application).

In the Recovery (Reply) section of Figure four, redo user transactions (redo user transactions) will be rerun on the user's new database or backup database to be consistent with the current selective operation, and the above undo User The transactions (undo user action) essentially corresponds to a transaction rollback that selectively rolls back operations over a period of time. Generally we need to set the save path of the SQL script separately. In the interface of Figure 4, the stop on error generally needs to be removed (error stops), include text, ntext, image data (contains binary data such as texts, long text, images, etc.), Retain Identity Values (Preserve the identification number, especially the increment seed), include transactions for all, no longer exist in the database schema (contains all tables not present in the DB schema).

Software downloads: Recovering SQL Server data with Log Explorer

Operation Manual Translation:

Part I. INTRODUCTION

Managing Microsoft SQL Server is a time-consuming task, and recovering data is a very important but extremely difficult task. Lumigent Log Explorer Products help you solve these challenges.

What is log Explorer.

Log Explorer is a major global solution for Microsoft SQL Server that integrates transaction analysis and data recovery. By providing independent SQL Server transaction log access, log Explorer can resolve and resolve most database problems by

L BROWSE the transaction log

L Export data for report generation

• Selective recovery of modified, deleted, lost, conflicting data

Applying bugs and user misuse can damage your data and cause your business to suffer a loss. The loss of fast positioning and recovery from errors is especially important for your business. Log Explorer can restore a Microsoft SQL Server database by online data repair avoid time-consuming technology. It is also worth adding that the degree of eventual recovery will not be lost in the recovery process for each successful transaction operation.

Log Explorer Pilot Database Manager in the product environment management, provide full log browsing, strong data recovery, data table restore, data export characteristics.

What Log Explorer does.

Log Explorer offers three key features:

• Powerful browsing data activity interactive access logging capabilities.

• Secure and reliable selective data recovery.

L Filtered log file export.

Browsing the transaction log

Use powerful browsing capabilities to quickly understand application behavior:

L Browse the transaction log (online, backed up SQL Server database log)

L Filter logs to provide custom views

L Select transactions based on time, type, table, username, spid, and other details

L Check all database transactions, operational information including DDL commands, session login information

L View all modification information for the specified table or specified row.

L View the user's interactive record with the system table.

∙ Statistical analysis based on the binding log

• Observing matters based on the binding log

Selective data Recovery

Use flexible data recovery capabilities to easily recover loss or damage from data loss or corruption.

L rollback specified table specified time

L rollback individual row exclude specified table specified time

L undo a transaction, successive transactions, and a single operation.

L Recursive series of Delete operations

L Redo a series of transactions

L Recover data from a missing or conflicting table

The restore feature uses a log-based rollback and redo method. The database was restored through backup and log without the use of additional traditional technology.

Recovery is selective. It fixes only those transactions that you set the conditions for, avoiding loss and unnecessary overhead.

Finally, the recovery function is secure. Log Explorer generates a secure SQL script to undo bad transaction operations or recover lost data. You can review and verify these scripts before applying to the active database.

Data export

You can use the Flexible data export feature to generate the following format for the selected log:

L XML, HTML, or other character-delimited file types

L SQL Server table

You can use exported data to generate a variety of reports through Third-party tools or custom Report Builder.

Why use log Explorer.

Log Explorer's powerful transactional analysis capabilities and data recovery capabilities make it much more profitable for database administrators, system administrators, application developers, QA engineers, IT Pros, and corporate data inspectors.

• Understand your application and the internal behavior of SQL Server

L significantly shorten the time to recover data

L greatly reduce the time of system paralysis

L restore lost or corrupted data

L selectively completely fix the selected object (without any extraneous data transactions)

L provide a rich level of repair

L Quickly find applications and user errors.

L Identify application behavior

• Monitoring System changes

L shorten the test cycle

L Analysis of the performance characteristics of the transaction

• Improve software Quality

Because Log Explorer uses a transaction log that is managed by the local database server, no more run-time overhead is required. Unlike, using a more cumbersome trigger-based solution, changes to application and data schemas need to be regenerated. Log Explorer operations do not have any impact on the server and do not require additional maintenance.

How Log Explorer works.

LOG Explorer uses client components and server-side components. The server-side component simply accesses the transaction log and provides the available log information to the client component. The client component interprets the filter string, analyzes it, and presents the log information to the user. You need to add that the client component also generates the SQL script for the restore based on the selection and the exported data to a file or SQL datasheet.

Figure 1 Landing window

Figure 2 Selecting a backup log file

Figure 3 Setting Filter parameters

Figure 4 Generating SQL scripts for redo user transactions



Log Explorer getting started.pdf (291 KB)
Logexplorer_sqlserver.rar (3,491 KB)
The attachment is only for the purpose of learning and exchanging. If you need commercial use, please purchase genuine.