Transaction log restore minor unexpected operation error

A few days ago a new set of procedures occurred an unexpected operation error, the programmer modifies the specified range of a table specified field, the error operation causes the update operation does not execute where, directly caused the table to specify the field all become Update field, in order to solve this problem, deliberately used the transaction log restore, Feel the need to write a process so that the new programmer encountered problems in the time not to rush, the following will be the specific process to write out everyone to see, a beginner look on the line.

1. First update before the data sheet

You can see that the second field is basically null.

2. Update and post-update situation

After the update, all record rows of the table are updated because no where condition is directly caused.

This is the time to be critical. First of all, do not panic at this time to do something that can not be restored error operation, etc., the 1th cool analysis there is no data backup. Fortunately, we have a good data backup mechanism, daily scheduled automatic data backup plan, and is a full backup, then how to deal with this problem.

3. The first step is to open the database backup transaction log, note that the option to remember the transaction log to select a backup to the end, and the database in a restored state.

3. You can then see this flag of the restore state database for database transaction restore

The point here is to select the time that the target time must be before the point in time for the wrong action, not the time of the transaction log backup and the time of the full backup.

4. View data table after restore

It is finally possible to return to the previous state. Thank God

Summarize:

in the actual environment is unavoidable error operation, even malicious operations caused by various database problems, this time it is necessary to remind us to establish a good backup mechanism, my suggestion is to make regular full backup of the production database, of course, in order to minimize misoperation and restore time, You can also add transaction log backups to improve efficiency during the full backup cycle. In addition, there is a point that I do not reflect here is also very important to the production database, when the transaction log restore, it is best to use a new test instance for the new DB instance for the data restore and then restore the results of the transaction log as a result of restoring the data table of the official database, This can not only repair the error operation, but also ensure the integrity of other table production data, minimizing the possibility of error.

Transaction log restore minor unexpected operation error